SYNCSORT - Matching records in 2 files



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

SYNCSORT - Matching records in 2 files

Postby 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!
Ed Chan
 
Posts: 1
Joined: Wed Mar 02, 2011 1:44 am
Has thanked: 0 time
Been thanked: 0 time

Re: SYNCSORT - Matching records in 2 files

Postby 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).
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: SYNCSORT - Matching records in 2 files

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

Here is a SyncSort for z/OS job that should produce the desired output:
//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,
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: SYNCSORT - Matching records in 2 files

Postby 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)?
Laine
 
Posts: 5
Joined: Thu Jul 02, 2009 2:30 am
Has thanked: 0 time
Been thanked: 0 time

Re: SYNCSORT - Matching records in 2 files

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

Yes. Here is a SyncSort for z/OS step that will accomplish this task:
//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)               
/*             
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: SYNCSORT - Matching records in 2 files

Postby Laine » Fri Mar 11, 2011 12:53 am

Alissa, Thank you! I think your solution will work for me.
Laine
 
Posts: 5
Joined: Thu Jul 02, 2009 2:30 am
Has thanked: 0 time
Been thanked: 0 time


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post