IBM Mainframe Forum

by **Ed Chan** » Wed Mar 02, 2011 2:39 am

I want to use SYNCSORT to match records in two files (FB/PS). The records in the two files, FILE1 and FILE2, all have two common keys for matching. When a match is found, the common key and one data field from each record are output to an output file, FILE3. Records in both FILE1 and FILE2 have a record length of 36 char.
The two common keys in the records of the two files are in pos. 1-11 and pos. 13-24. FILE1 has a data field in pos 26-30 and blanks in pos. 31-36.
FILE2 has a data field in pos. 31-36 and blanks in 26-30.
Can someone provide a sample JCL to show how FILE3 is generated below using SYNCSORT?

FILE1
00000111111 000001234567 LOCAL =====
00000333333 000003456789 LOCAL =====
00000555555 000005678901 EXTRN =====
00000666666 000006789012 EXTRN =====
00000777777 000007890123 LOCAL =====

FILE2
00000222222 000002345678 ===== BCDEF
00000444444 000004567890 ===== DEFGH
00000666666 000006789012 ===== FGHIJK
00000888888 000008901234 ===== HIJKLM
00000999999 000009012345 ===== JKLLMN

FILE3 (Output)
00000666666 000006789012 EXTRN FGHIJK

Many thanks!

by **dick scherrer** » Wed Mar 02, 2011 3:14 am

Hello and welcome to the forum,

Suggest you search the Syncsort part of the forum for topics that have JOINKEYS in the text. There are multiple examples of this that you should be able to work with.

If you are not familiar with JOINKEYS, this is from Alissa Margulies (of Syncsort):

The JOINKEYS control statement is used to enable join feature processing and to identify the fields used to select records for join processing. The join feature joins records from two input files that are specified on the SORTJNF1 and SORTJNF2 DD statements. For complete details regarding SyncSort's join processing, please refer to the SyncSort for z/OS 1.3 Programmer's Guide.

If you put something together that abends or otherwise does not work as you want, post what you submitted and the informational/diagnostic info generated (including message ids).

by **Alissa Margulies** » Fri Mar 04, 2011 1:31 am

Here is a SyncSort for z/OS job that should produce the desired output:

Select all

//SORT1 EXEC PGM=SORT
//SORTJNF1 DD *
00000111111 000001234567 LOCAL =====
00000333333 000003456789 LOCAL =====
00000555555 000005678901 EXTRN =====
00000666666 000006789012 EXTRN =====
00000777777 000007890123 LOCAL =====
//SORTJNF2 DD *
00000222222 000002345678 ===== BCDEF
00000444444 000004567890 ===== DEFGH
00000666666 000006789012 ===== FGHIJ
00000888888 000008901234 ===== HIJKL
00000999999 000009012345 ===== JKLMN
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,11,A,13,12,A)
JOINKEYS FILES=F2,FIELDS=(1,11,A,13,12,A)
REFORMAT FIELDS=(F1:1,31,F2:32,5)
SORT FIELDS=COPY
/*

Please let us know if this works for you or if you require further assistance.

Regards,

by **Laine** » Thu Mar 10, 2011 10:40 pm

I have similar requirements. Alissa, your solution is working but in addition to writing out the matches I would also like to write out the non mathces:
In file1, not in file2 (write to file3)
In file2, not in file1 (write to file4)

In your code above, I believe, only one records gets written out:
00000666666 000006789012 EXTRN FGHIJ (write to file5)

So file3 would contain:
00000111111 000001234567 LOCAL =====
00000333333 000003456789 LOCAL =====
00000555555 000005678901 EXTRN =====
00000777777 000007890123 LOCAL =====

File4:
00000222222 000002345678 ===== BCDEF
00000444444 000004567890 ===== DEFGH
00000888888 000008901234 ===== HIJKL
00000999999 000009012345 ===== JKLMN

and File5:
00000666666 000006789012 EXTRN FGHIJ

Can this be accomplished via syncsort (SYNCSORT FOR Z/OS 1.3.1.1R)?

by **Alissa Margulies** » Thu Mar 10, 2011 11:31 pm

Yes. Here is a SyncSort for z/OS step that will accomplish this task:

Select all

//SORT1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
00000111111 000001234567 LOCAL =====
00000333333 000003456789 LOCAL =====
00000555555 000005678901 EXTRN =====
00000666666 000006789012 EXTRN =====
00000777777 000007890123 LOCAL =====
//SORTJNF2 DD *
00000222222 000002345678 ===== BCDEF
00000444444 000004567890 ===== DEFGH
00000666666 000006789012 ===== FGHIJ
00000888888 000008901234 ===== HIJKL
00000999999 000009012345 ===== JKLMN
//SORTOF01 DD DSN=FILE1.ONLY,DISP=...
//SORTOF02 DD DSN=FILE2.ONLY,DISP=..
//SORTOF03 DD DSN=FILE1.AND.FILE2,DISP=...
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,11,A,13,12,A)
JOINKEYS FILES=F2,FIELDS=(1,11,A,13,12,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,36,F2:1,36),FILL=(C'#')
SORT FIELDS=COPY
OUTFIL FILES=01,INCLUDE=(37,1,CH,EQ,C'#'),BUILD=(1,36)
OUTFIL FILES=02,INCLUDE=(1,1,CH,EQ,C'#'),BUILD=(37,36)
OUTFIL FILES=03,SAVE,BUILD=(1,31,68,5)
/*

by **Laine** » Fri Mar 11, 2011 12:53 am

Alissa, Thank you! I think your solution will work for me.

IBM Mainframe Forum

SYNCSORT - Matching records in 2 files

SYNCSORT - Matching records in 2 files

Re: SYNCSORT - Matching records in 2 files

Re: SYNCSORT - Matching records in 2 files

Re: SYNCSORT - Matching records in 2 files

Re: SYNCSORT - Matching records in 2 files

Re: SYNCSORT - Matching records in 2 files