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

 

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


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post