Join Paired Problem



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Join Paired Problem

Postby Teja » Wed Oct 19, 2011 8:21 pm

Hi,

I have two files, both have LRECL=849 and RECFM=FB.
I wanted to extract ONLY Matching records from Second File, the criteria for matching is 44,22.
File 1 has 67990 Records and File 2 has 104202 Records.
Below is the Sort Card I have used.
//SORTJNF1 DD DISP=SHR,DSN=FILE1 
//SORTJNF2 DD DISP=SHR,DSN=FILE2
//SYSIN :                               
  JOINKEYS FILE=F1,FIELDS=(44,22,A)   
  JOINKEYS FILE=F2,FIELDS=(44,22,A)   
  REFORMAT FIELDS=(F2:1,849)           
  SORT FIELDS=COPY                     

Below is the messages from SORTLIST in SYSOUT
WER901I  **WARNING** SYNCSORT 1.4.0.0 WILL EXPIRE IN 53 DAYS                   
WER481I  JOINKEYS REFORMAT RECORD LENGTH=  849, TYPE = F                       
WER110I  SORTOUT  : RECFM=FB   ; LRECL=   849; BLKSIZE= 27168                 
WER074I  SORTOUT  : DSNAME=OUTFILE                   
WER054I  RCD IN       1507, OUT       1507                                 
WER072I  NOEQUALS, BALANCE IN EFFECT                                           
WER169I  RELEASE 1.4 BATCH 0518 TPF LEVEL 0.0                                 
WER482I  JNF1 STATISTICS                                                       
WER108I  SORTJNF1 : RECFM=FB   ; LRECL=   849; BLKSIZE= 27168                 
WER073I  SORTJNF1 : DSNAME=FILE1                   
WER484I  SORTJNF1 : RCD IN= 67990,OMITTED= 0,PAIRED= 825,UNPAIRED= 67165       
WER487I  FILESIZE 57,723,510 BYTES                                             
WER482I  JNF2 STATISTICS                                                       
WER108I  SORTJNF2 : RECFM=FB   ; LRECL=   849; BLKSIZE= 27168                 
WER073I  SORTJNF2 : DSNAME=FILE2                     
WER484I  SORTJNF2 : RCD IN= 104202,OMITTED= 0,PAIRED= 991,UNPAIRED= 103211     
WER487I  FILESIZE 88,467,498 BYTES                                             


Can someone please let me know why I got 1507 records when there are only 991 Paired records in File2.
Do i need to change my SORT CARD to get only Matching Records from FILE2.
Teja
 
Posts: 6
Joined: Wed Oct 19, 2011 12:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join Paired Problem

Postby BillyBoyo » Thu Nov 10, 2011 5:16 am

This seems to have slipped behind the filing cabinet.

There is a Syncsort forum here.

Consult your documentation. You now have quite a few days less than 53 to do so.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Join Paired Problem

Postby dick scherrer » Thu Nov 10, 2011 10:12 am

Hello and welcome to the forum,

Can there be duplicates in either/both files?

What happens if you specify JOIN PAIRED? I'm not able to test this tonight, but it might show something. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Join Paired Problem

Postby pjagathis » Mon Nov 14, 2011 3:57 pm

HI,

This is due to duplicate records in FILE1...

Please remove duplicate records in driver file(File 1) based on field(44,22), then give the deduped file as input...

So, you will get the correct count in output...
Thanks and Regards,
Jagathis P
pjagathis
 
Posts: 67
Joined: Wed May 04, 2011 5:04 pm
Location: Chennai
Has thanked: 0 time
Been thanked: 0 time

Re: Join Paired Problem

Postby Teja » Tue Dec 27, 2011 5:15 pm

Hi,

Thanks for the reply. The data had duplicate records.
When I tried giving 'JOIN PAIRED' as suggested by Dick, I got the below error message
 SYSIN :                                           
   JOINKEYS FILE=F1,FIELDS=(44,22,A)               
   JOINKEYS FILE=F2,FIELDS=(44,22,A)               
   JOIN PAIRED                                     
        *                                           
   REFORMAT FIELDS=(F2:1,849)                       
   SORT FIELDS=COPY                                 
 WER268A  JOIN STATEMENT    : SYNTAX ERROR         


I have obtained the required output using two 'JOIN UNPAIRED' Steps since the data had duplicates.

However, I want to know if there is anyway to handle this situation when the data has duplicates.

Thanks in advance,
M. Hari Teja
Teja
 
Posts: 6
Joined: Wed Oct 19, 2011 12:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join Paired Problem

Postby BillyBoyo » Tue Dec 27, 2011 5:30 pm

I don't have SyncSort documentation.

You get "paired" by default from the join. I don't think you can actually specify it explicitly.

You could consult your documentation, I'm sure they have examples, for the join using pre-processing of one/both files. That way you can remove the duplicates in the same step.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Join Paired Problem

Postby Teja » Wed Dec 28, 2011 4:50 pm

Thanks BillyBoyo.

I will check the documentation and try to findout if there is anyway to handle this situation when the data has duplicates.
Teja
 
Posts: 6
Joined: Wed Oct 19, 2011 12:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join Paired Problem

Postby Alissa Margulies » Tue Jan 31, 2012 2:37 am

Try the following:

//SYSIN DD *                               
  JOINKEYS FILE=F1,FIELDS=(44,22,A)   
  JOINKEYS FILE=F2,FIELDS=(44,22,A)   
  REFORMAT FIELDS=(F2:1,849)           
  SORT FIELDS=(44,22,CH,A)
  DUPKEYS FIELDS=NONE
/*


Regards,
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times

Re: Join Paired Problem

Postby Teja » Tue Feb 07, 2012 8:20 pm

Thank you very much, Alissa. This worked for me.
Teja
 
Posts: 6
Joined: Wed Oct 19, 2011 12:50 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Join Paired Problem

Postby pjagathis » Thu Feb 23, 2012 6:49 pm

Hi Alissa,

Whether it will remove duplicated from File1 or File2?
Thanks and Regards,
Jagathis P
pjagathis
 
Posts: 67
Joined: Wed May 04, 2011 5:04 pm
Location: Chennai
Has thanked: 0 time
Been thanked: 0 time

Next

Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post