Joinkeys - Wrong O/P



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

Joinkeys - Wrong O/P

Postby rockstar2020 » Wed Jan 14, 2015 12:14 am

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)         
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Joinkeys - Wrong O/P

 

Re: Joinkeys - Wrong O/P

Postby NicC » Wed Jan 14, 2015 3:22 pm

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?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2702
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Joinkeys - Wrong O/P

Postby BillyBoyo » Wed Jan 14, 2015 6:25 pm

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".
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Joinkeys - Wrong O/P

Postby rockstar2020 » Wed Jan 14, 2015 8:14 pm

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.
rockstar2020
 
Posts: 32
Joined: Wed Jan 08, 2014 3:51 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Joinkeys - Wrong O/P

Postby BillyBoyo » Wed Jan 14, 2015 8:40 pm

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)       
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post