Join two files, replacing file1 values with file2 values



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Join two files, replacing file1 values with file2 values

Postby BGronli » Thu Sep 20, 2012 8:12 pm

I have two files, both LRECL=455, both with keys 1,32,A,81,39,A. Where the keys match, I need to replace the values from file2 33,38,CH in file1, same starting spot and length. I used the following code as a basis,
//STEP0100 EXEC PGM=SORT                               
//SYSOUT   DD SYSOUT=*                                 
//INA      DD DISP=SHR,DSN=Your Input FB 600 Byte file
//INB      DD DISP=SHR,DSN=Your Input FB 80 Byte file
//SORTOUT  DD SYSOUT=*                                 
//SYSIN    DD *                                         
  OPTION COPY                                           
  JOINKEYS F1=INA,FIELDS=(06,9,A)                       
  JOINKEYS F2=INB,FIELDS=(10,9,A)                       
  JOIN UNPAIRED,F1                                     
  REFORMAT FIELDS=(F1:1,600,?,F2:1,9)                   
  INREC IFOUTLEN=600,                                   
  IFTHEN=(WHEN=(601,1,CH,EQ,C'B'),OVERLAY=(20:602,9))   
//*

to get to
//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=A                                           
//INA      DD DISP=SHR,DSN=D.WIT.WRK.ALMTBALS                   
//INB      DD DISP=SHR,DSN=D.WIT.WRK.ALMT.BALS                   
//SORTOUT  DD SYSOUT=A                                           
//SYSIN    DD *                                                 
  OPTION COPY                                                   
  JOINKEYS F1=INA,FIELDS=(1,32,A,81,39,A)                       
  JOINKEYS F2=INB,FIELDS=(1,32,A,81,39,A)                       
  JOIN UNPAIRED,F1                                               
  REFORMAT FIELDS=(F1:1,455,?,F2:33,48)                         
  INREC IFOUTLEN=455,                                           
  IFTHEN=(WHEN=(456,1,CH,EQ,C'B'),OVERLAY=(33:457,48))           
/*

In the IFTHEN, why should it be looking for a B in position 456? Also, do I have this setup correctly to replace values from file2 in file1 when the keys match and to keep unpaired records in file1?

Thank you,
Brad
BGronli
 
Posts: 5
Joined: Tue Sep 11, 2012 6:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join two files, replacing file1 values with file2 values

Postby skolusu » Thu Sep 20, 2012 9:51 pm

BGronli wrote:I have two files, both LRECL=455, both with keys 1,32,A,81,39,A. Where the keys match, I need to replace the values from file2 33,38,CH in file1, same starting spot and length. I used the following code as a basis,
In the IFTHEN, why should it be looking for a B in position 456? Also, do I have this setup correctly to replace values from file2 in file1 when the keys match and to keep unpaired records in file1?
Thank you,
Brad


BGronli,

Your syntax cards are correct. As for your question as to why you need to validate the indicator at position 456, you used JOIN UNPAIRED,F1 which writes out unpaired records from F1 as well as paired records. For paired records you can replace the contents from file 2 but for unpaired records , DFSORT does not know what to replace , so it fills with the default FILL character which is spaces thus by overlaying the original content. In order to preserve the original content we add an indicator at the end and also the file2 contents in a temp location (usually at the end) and then we validate the indicator to overlay file 2 values on to file1.

? on the REFORMAT statement indicates a 1-byte indicator is to be included in each joined record. The indicator will be set to one of the following values in each paired or unpaired joined record, as appropriate:

'B' - the key was found in F1 and F2.

'1' - the key was found in F1, but not in F2.

'2' - the key was found in F2, but not in F1.

So B denotes a matching record and we are only overlaying the contents of file2 using IFTHEN statement.

Check this link for a detailed explanation of JOINKEYS

http://publibz.boulder.ibm.com/cgi-bin/ ... E1CA60/4.0
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Join two files, replacing file1 values with file2 values

Postby BGronli » Thu Mar 07, 2013 2:22 am

skolusu,

Thank you for your earlier response and I apologize for not returning sooner to do so. With regards to your example, should the output file be larger than the file being updated? When I run the following:
//SORT4    EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=A                                   
//SORTJNF1 DD DSN=D.WIT.WRK.ALMTBALS,DISP=SHR             
//SORTJNF2 DD DSN=D.WIT.WRK.ALMT.BALS,DISP=SHR           
//SORTOUT  DD DSN=D.WIT.WRK.ALMT.BALS2,DISP=(NEW,CATLG), 
//            DCB=(RECFM=FB,BLKSIZE=0)                   
//SYSIN    DD *                                           
  OPTION COPY                                             
  JOINKEYS F1=SORTJNF1,FIELDS=(1,32,A,81,11,A)           
  JOINKEYS F2=SORTJNF2,FIELDS=(1,32,A,81,11,A)           
  JOIN UNPAIRED,F1                                       
  REFORMAT FIELDS=(F1:1,455,?,F2:33,48)                   
  INREC IFOUTLEN=455,                                     
  IFTHEN=(WHEN=(456,1,CH,EQ,C'B'),OVERLAY=(33:457,48))   
/*                                                       
SORTJNF1 has 823,433 records. SORTJNF2 has 54,627 records. I would think that SORTOUT should also have 823,433 records, however, it has 1,089,719. Any ideas?
BGronli
 
Posts: 5
Joined: Tue Sep 11, 2012 6:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join two files, replacing file1 values with file2 values

Postby skolusu » Thu Mar 07, 2013 2:29 am

BGronli wrote:skolusu,

SORTJNF1 has 823,433 records. SORTJNF2 has 54,627 records. I would think that SORTOUT should also have 823,433 records, however, it has 1,089,719. Any ideas?


I am guessing that you have duplicates on the keys you matching in both files. For ex: if you have 3 duplicates in file1 for a key and file2 has 5 duplicates for the same key, the resulting output will be 3 X 5 = 15 rows.

So Check if you have duplicates and if you don't want a cartesian product , then you can eliminate duplicates using JNF1 or JNF2. Let me know if that is what you want and I will show you a way to do it.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Join two files, replacing file1 values with file2 values

Postby BGronli » Thu Mar 07, 2013 3:11 am

I did find duplicates and the problem was in the
  JOINKEYS F1=SORTJNF1,FIELDS=(1,32,A,81,11,A)           
  JOINKEYS F2=SORTJNF2,FIELDS=(1,32,A,81,11,A)           
statements. They both should have been (1,32,A,81,39,A). Once I corrected that and ran, I have the correct number in the SORTOUT, 823,433.

Thanks again for the help.
BGronli
 
Posts: 5
Joined: Tue Sep 11, 2012 6:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join two files, replacing file1 values with file2 values

Postby Oreod123 » Mon Jul 01, 2019 10:02 pm

I had a similar question in another post and had used the above sort/join suggestion.. I wanted to thank users Skolusu and BGronli for the great help... Also I'm not sure how to thank them and that is why I was posting in the thread!
Oreod123
 
Posts: 4
Joined: Sat Jun 29, 2019 1:35 am
Has thanked: 1 time
Been thanked: 0 time

Re: Join two files, replacing file1 values with file2 values

Postby NicC » Tue Jul 02, 2019 2:36 pm

There is a "thumbs up" icon on the right of the row of icons above a post.

but...Greod123 has not been seen in years and Kolusu, who is a DFSort developer, does not participate in these forums but has his own.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic

These users thanked the author NicC for the post:
Oreod123 (Tue Jul 02, 2019 5:52 pm)
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post