Page 1 of 1

Joinkeys - Wrong O/P

PostPosted: Wed Jan 14, 2015 12:14 am
by rockstar2020
My requirement is simple. I have 2 files. File 1 is primary. i want two o/p files . Matched records in F1,F2 and unpaired records from F1 only.

F1 -
aaa 123456
bbb 111111
ccc 333333


F2 -
bbb 111111
ccc 333333
eee 555555


I'm referring the below link to perform this
http://www-01.ibm.com/support/knowledge ... ethod_.htm

Am using below code, however am getting same records in both the output files as that of F1. I dont understand why am not getting desired output.

//SORTJNF1 DD DSN=F1,DISP=SHR       
//SORTJNF2 DD DSN=F2,DISP=SHR 
//F1ONLY   DD SYSOUT=*                                               
//BOTH     DD DSN=Out-file,                       
//   DISP=(,CATLG,DELETE),                                       
//   UNIT=APGGRP,SPACE=(CYL,(1000,1000),RLSE)       
//SYSOUT DD SYSOUT=*                                                 
//SYSIN DD *                                                         
  JOINKEYS FILE=F1,FIELDS=(1,3,A)                                   
  JOINKEYS FILE=F2,FIELDS=(1,3,A)                                   
  JOIN UNPAIRED,F1,F2                                               
  REFORMAT FIELDS=(F1:1,10)
  OPTION COPY                                                       
    OUTFIL FNAMES=F1ONLY,BUILD=(1,10)       
    OUTFIL FNAMES=BOTH,BUILD=(1,10)         

Re: Joinkeys - Wrong O/P

PostPosted: Wed Jan 14, 2015 3:22 pm
by NicC
Well, (not being a sort expert, let alone joinkeys but comparing your code to the example mentioned) how about the INCLUDE keywords that you dropped?

Re: Joinkeys - Wrong O/P

PostPosted: Wed Jan 14, 2015 6:25 pm
by BillyBoyo
Nicc is correct. On the OUTFILs that you have coded, F1ONLY and BOTH are just DDnames. They have no processing significance. So you have no selection of data. So you will get two identical copies.

Your current outputs will include unmatched F2 records as well. If you only want matched and unmatched F1, then:

 JOIN UNPAIRED,F1


You need to be able to distinguish between matched and unmatched, so use the special marker, which is a question-mark, ?, on the REFORMAT statement.

 REFORMAT FIELDS=(F1:1,10,?)


That means the 11th byte of your record will contain B (for Both) or 1 (for F1 only). You will need to use BUILD on both OUTFILs to drop off the extra byte.

If you use one INCLUDE= on one OUTFIL, you can use SAVE on the other, which will give you "all records which don't already appear on another OUTFIL".

Re: Joinkeys - Wrong O/P

PostPosted: Wed Jan 14, 2015 8:14 pm
by rockstar2020
Thanks Nicc and BIll. I tried using the suggestion but still not getting desired o/p. I'm not sure whats wrong in my code.
    //SORTJNF1 DD DSN=F1,DISP=SHR       
    //SORTJNF2 DD DSN=F2,DISP=SHR
    //F1ONLY   DD SYSOUT=*                                               
    //BOTH     DD DSN=Out-file,                       
    //   DISP=(,CATLG,DELETE),                                       
    //   UNIT=APGGRP,SPACE=(CYL,(1000,1000),RLSE)       
    //SYSOUT DD SYSOUT=*                                                 
    //SYSIN DD *                                                         
      JOINKEYS FILE=F1,FIELDS=(1,3,A)                                   
      JOINKEYS FILE=F2,FIELDS=(1,3,A)                                   
      JOIN UNPAIRED,F1                                           
      REFORMAT FIELDS=(F1:1,10,?)
      OPTION COPY                                                       
        OUTFIL FNAMES=F1ONLY,BUILD=(1,10)       
        OUTFIL FNAMES=BOTH,BUILD=(1,10)


I didnt use INCLUDE as there is no need.
O/p of above JCL is still all the F1 records in BOTH and F1ONLY files. I'm not getting paired and unpaired records.
When i change to JOIN UNPAIRED,F1,ONLY, I get unpaired records from F1 file into BOTH and F1ONLY files.

Re: Joinkeys - Wrong O/P

PostPosted: Wed Jan 14, 2015 8:40 pm
by BillyBoyo
You must use INCLUDE= (or OMIT=) as there is an absolute necessity if you want the OUTFILs to contain different content.

You state that you don't want the same content.

So:

      JOINKEYS FILE=F1,FIELDS=(1,3,A)                                   
      JOINKEYS FILE=F2,FIELDS=(1,3,A)                                   
      JOIN UNPAIRED,F1                                           
      REFORMAT FIELDS=(F1:1,10,?)
      OPTION COPY                                                       
        OUTFIL FNAMES=F1ONLY,
               INCLUDE=(11,1,CH,EQ,C'1'),
               BUILD=(1,10)       
        OUTFIL FNAMES=BOTH,
               SAVE,
               BUILD=(1,10)