MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Merge data from 2 files into one

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
tcurrier
Intermediate


Joined: 10 Feb 2006
Posts: 188
Topics: 68
PostPosted: Fri Jun 03, 2011 7:07 am    Post subject: Merge data from 2 files into one Reply with quote
I've seen more complicated examples of this being done, but I'm having trouble understanding all of the various parameters being used, but for a simple example, can anyone show me how to merge data from two input files into 1 file with the specified fields merged into the output records ?

Code:
//S1      EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*     
//DFSMSG  DD SYSOUT=*     
//IN1 DD *                 
AAA  BILL JONES           
BBB  FRED SMITH           
CCC  JOHN DOE             
/*                         
//IN2 DD *                 
AAA  PITTSBURGH           
BBB  HOUSTON               
CCC  MIAMI                 
/*                         


Code:
Desired output:

AAA BILL JONES           PITTSBURGH
BBB FRED SMITH           HOUSTON
CCC JOHN DOE             MIAMI


Thanks.
Back to top
View user's profile Send private message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Fri Jun 03, 2011 9:34 am    Post subject: Reply with quote
tcurrier,
If it is record by record match,meaning 1st record of file1 goes with 1st record file2, then see if below helps.. I assumed your input file is FB,LRECL=80.

Code:
//STEP0001 EXEC PGM=SORT                 
//SYSOUT   DD SYSOUT=*                   
//SORTJNF1 DD *                           
AAA  BILL JONES                           
BBB  FRED SMITH                           
CCC  JOHN DOE                             
//SORTJNF2 DD *                           
AAA  PITTSBURGH                           
BBB  HOUSTON                             
CCC  MIAMI                               
//SORTOUT  DD  SYSOUT=*                   
//SYSIN DD *                             
  JOINKEYS FILE=F1,FIELDS=(81,08,A)       
  JOINKEYS FILE=F2,FIELDS=(81,08,A)       
  REFORMAT FIELDS=(F1:01,025,F2:6,20)     
  SORT FIELDS=COPY                       
/*                                       
//JNF1CNTL DD *                           
 INREC OVERLAY=(81:SEQNUM,8,ZD)           
/*                                       
//JNF2CNTL DD *                           
 INREC OVERLAY=(81:SEQNUM,8,ZD)           
/*         


OUTPUT
Code:

AAA  BILL JONES          PITTSBURGH
BBB  FRED SMITH          HOUSTON   
CCC  JOHN DOE            MIAMI     


Thanks,
Back to top
View user's profile Send private message
tcurrier
Intermediate


Joined: 10 Feb 2006
Posts: 188
Topics: 68
PostPosted: Fri Jun 03, 2011 10:07 am    Post subject: Reply with quote
Thanks, Sqlcode....

I was able to get it to work when there wasn't a one-for-one match
by using:

JOINKEYS FILE=F1,FIELDS=(1,3,A)
JOINKEYS FILE=F2,FIELDS=(1,3,A)
Back to top
View user's profile Send private message
Sqlcode
Intermediate


Joined: 15 Dec 2006
Posts: 157
Topics: 38
PostPosted: Fri Jun 03, 2011 10:48 am    Post subject: Reply with quote
tcurrier,
I am sure you must have taken care of this already but in that case, you don't need JNF1CNTL and JNF2CNTL.

Thanks,
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose
PostPosted: Fri Jun 03, 2011 12:09 pm    Post subject: Reply with quote
tcurrier,

For complete information on DFSORT's JOINKEYS function, see:

https://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174

For examples of using JOINKEYS for various matching applications, see the "Create files with matching and non-matching records" Smart DFSORT Trick at:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000094
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
tcurrier
Intermediate


Joined: 10 Feb 2006
Posts: 188
Topics: 68
PostPosted: Fri Jun 03, 2011 2:44 pm    Post subject: Reply with quote
Thanks Frank... one last question...

I want to be able to place the output fields in specific positions on the
output record. For example, currently the name and city start in positions 6 and 25, but I want to move them to positions 15 and 40.

Code:
----+----1----+----2----+----3----+----4----+----5-
***************************** Top of Data *********
CURRENT:                                           
                                                   
AAA  BILL JONES          PITTSBURGH               
BBB  FRED SMITH          HOUSTON                   
CCC  JOHN DOE            MIAMI                     
                                                   
                                                   
REVISED:                                           
                                                   
AAA           BILL JONES               PITTSBURGH 
BBB           FRED SMITH               HOUSTON     
CCC           JOHN DOE                 MIAMI       
**************************** Bottom of Data *******



How do I account for this adjustment ? My current control cards are:

Code:
  JOINKEYS FILE=F1,FIELDS=(1,3,A)   
  JOINKEYS FILE=F2,FIELDS=(1,3,A)   
  JOIN UNPAIRED,F1                   
  REFORMAT FIELDS=(F1:01,025,F2:6,20)
  SORT FIELDS=COPY           
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
PostPosted: Fri Jun 03, 2011 2:50 pm    Post subject: Reply with quote
tcurrier,

Add the following statement to format the joined record
Code:

  INREC BUILD=(1,3,15:6,20,40:26,20)       

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
tcurrier
Intermediate


Joined: 10 Feb 2006
Posts: 188
Topics: 68
PostPosted: Fri Jun 03, 2011 4:28 pm    Post subject: Reply with quote
This is about the coolest utility/function there is. To be able to
merge data from multiple files into one is priceless.
(Yeah, try doing that easily with a COBOL program!)

Thanks to everyone for your help!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group