IBM Mainframe Forum

Posted: **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.

Select all

//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

Select all

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.

Posted: **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.

Posted: **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. . .

Posted: **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...

Posted: **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

Select all

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

Posted: **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.

Posted: **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.

Posted: **Tue Jan 31, 2012 2:37 am**

Try the following:

Select all

//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,

Posted: **Tue Feb 07, 2012 8:20 pm**

Thank you very much, Alissa. This worked for me.

Posted: **Thu Feb 23, 2012 6:49 pm**

Hi Alissa,

Whether it will remove duplicated from File1 or File2?

IBM Mainframe Forum

Join Paired Problem

Join Paired Problem

Re: Join Paired Problem

Re: Join Paired Problem

Re: Join Paired Problem

Re: Join Paired Problem

Re: Join Paired Problem

Re: Join Paired Problem

Re: Join Paired Problem

Re: Join Paired Problem

Re: Join Paired Problem