Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

HELP in merging two files

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Roshnii

New User


Joined: 30 Sep 2008
Posts: 45
Location: bangalore

PostPosted: Wed Jul 01, 2009 3:16 pm    Post subject: HELP in merging two files
Reply with quote

I have two tables , table A and table B.
Table b is the child of table a. I need to unload the data
Of both the tables and put it in a file.
Say the data in the tables are like this

TABLE A

ID(PK) NAME SEMESTER
001 THOMAS THIRD
002 SMITH FOURTH
003 JOHN FIRST


TABLE B
ID(PK) SEQ(PK) SUBJECTS
001 1 MATHS
001 2 GEOGRAPHY
001 3 PSYCHOLOGY
002 1 HISTORY
003 1 ENGLISH
003 2 ELECTRONICS

OUTPUT FILE
A.ID A.NAME A.SEMESTER B.ID B.SEQ B.SUBJECTS
001 THOMAS THIRD 001 1 MATHS
001 THOMAS THIRD 001 2 GEOGRAPHY
001 THOMAS THIRD 001 3 PSYCHOLOGY
002 SMITH FOURTH 002 1 HISTORY
003 JOHN FIRST 003 1 ENGLISH
003 JOHN FIRST 003 2 ELECTRONICS

The output should be such that when there are multiple TABLE B entries then one record would be created for each TABLE B with the TABLE A repeating at the beginning or each record.

I do an unload of the data from these two tables and keep them in two files. Then while merging, is it possible to obtain the above results using sort?
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Wed Jul 01, 2009 3:25 pm    Post subject:
Reply with quote

The usual questions ........................
For both input files - what is the RECFM and LRECL.
In which position and format are the key fields.
Back to top
View user's profile Send private message
Roshnii

New User


Joined: 30 Sep 2008
Posts: 45
Location: bangalore

PostPosted: Wed Jul 01, 2009 3:38 pm    Post subject: Reply to: HELP in merging two files
Reply with quote

Both the files are FB and the rec length for file A is 38.
and for file B is 35
and the key for table A is ID which is defined as DECIMAL(18,0) in the database
and the key for table B are ID and seq number which are defined as DECIMAL(18,0) and SMALLINT
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Wed Jul 01, 2009 3:55 pm    Post subject:
Reply with quote

Where the key fields live on the database is of no consequence, it is where they are in the unloaded file that matters.
Back to top
View user's profile Send private message
sudhakarreddy.ibm
Warnings : 1

New User


Joined: 20 Apr 2009
Posts: 15
Location: chennai

PostPosted: Wed Jul 01, 2009 3:57 pm    Post subject:
Reply with quote

Hello,

I think, following query will help you to get your required output.

select A.ID, A.NAME, A.SEMESTER, B.ID, B.SEQ, B.SUBJECTS from A JOIN B ON A.ID=B.ID
WHERE GROUP BY A.ID,B.ID

Please correct me if i am wrong?

Sudha................
Back to top
View user's profile Send private message
sudhakarreddy.ibm
Warnings : 1

New User


Joined: 20 Apr 2009
Posts: 15
Location: chennai

PostPosted: Wed Jul 01, 2009 4:15 pm    Post subject:
Reply with quote

Inaccurate response removed - Expat
Back to top
View user's profile Send private message
Roshnii

New User


Joined: 30 Sep 2008
Posts: 45
Location: bangalore

PostPosted: Wed Jul 01, 2009 4:41 pm    Post subject: Reply to: HELP in merging two files
Reply with quote

I will be unloading the data from table A to file A which is of length 38 and from table B to a file B which is of 35
and the the first 18 characters of each file will be the key X(18). I hope my answer is clear.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jul 01, 2009 11:49 pm    Post subject:
Reply with quote

roshnii wrote:
I will be unloading the data from table A to file A which is of length 38 and from table B to a file B which is of 35
and the the first 18 characters of each file will be the key X(18). I hope my answer is clear.


roshnii,

In your earlier post you mentioned your key is defined as DECIMAL(18,0) which is equivalent to 10 PD field. If you are unloading it it would be just 10 byte PD field and not 18 byte character field.

The Following DFSORT/ICETOOL JCl will give you the desired results. This will give all the matching key records with the value from the parent table inserted in the first 38 bytes followed by child table data making 73 byte file (38+35 bytes)

Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                               
//DFSMSG   DD SYSOUT=*                                               
//I1       DD DSN=your 38byte parent table file,DISP=SHR
//I2       DD DSN=your 35byte child table file,DISP=SHR
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)       
//OUT      DD SYSOUT=*                                               
//TOOLIN   DD *                                                     
  COPY FROM(I1) USING(CTL1)                                         
  COPY FROM(I2) USING(CTL2)                                         
  SORT FROM(T1) USING(CTL3)                                         
//CTL1CNTL DD *                                                     
  OUTFIL FNAMES=T1,OVERLAY=(39:C'$')                                 
//CTL2CNTL DD *                                                     
  OUTFIL FNAMES=T1,OVERLAY=(39:X)                                   
//CTL3CNTL DD *                                                     
  SORT FIELDS=(1,10,PD,A)                                           
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(40:SEQNUM,8,ZD,RESTART=(1,10))),
  IFTHEN=(WHEN=GROUP,BEGIN=(40,8,ZD,EQ,1),PUSH=(39:39,1,48:11,28))   
  OUTFIL FNAMES=OUT,BUILD=(1,10,48,28,1,35),                         
  OMIT=(39,1,CH,EQ,C'$',AND,40,8,ZD,EQ,1)
/*
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us