IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Syncsort two-file key match
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
jaganmoni1

New User


Joined: 31 Oct 2008
Posts: 35
Location: CANADA
PostPosted: Tue Nov 29, 2011 2:41 am
 Reply with quote
File1 (Key file) and File2 (Master file) has unique records and in sorted order.
File1 attributes:
LRECL – 12
RECFM – FB
Key Position – column 1 to column 12

File2 attributes:
LRECL – 2200
RECFM – VB
Key Position – column 1 to column 12


File 1 sample date:
----+----1--
************
000260006106
000260006188
000260006256
001410009941
001410009942
001410009943
001410009944
001410009945
001410009946
001410009947
001410009948
001410009949

If file1 key (column: 1 to 12) has match in file2 (column: 1 to 12), then I need file2 record in output1 file.
If file1 key has no match in file2, then I need file1 record in output2 file.
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 496
Location: USA
PostPosted: Tue Dec 06, 2011 3:21 am
 Reply with quote
Jaganmoni1,

Here is SyncSort for z/OS job that should produce your requested output:
Code:
//SORT   EXEC PGM=SORT 
//SORTJNF1 DD DISP=SHR,DSN=FILE1       
//SORTJNF2 DD DISP=SHR,DSN=FILE2       
//SORTOUT  DD SYSOUT=* 
//SYSOUT   DD SYSOUT=*   
//SYSIN    DD *   
  JOINKEYS FILES=F1,FIELDS=(1,12,A) 
  JOINKEYS FILES=F2,FIELDS=(1,12,A) 
  JOIN UNPAIRED,F1                   
  REFORMAT FIELDS=(F1:1,12,F2:13,2188)
  SORT FIELDS=COPY                   
/*

Regards,
Back to top
View user's profile Send private message
gcicchet

Senior Member


Joined: 28 Jul 2006
Posts: 1702
Location: Australia
PostPosted: Tue Dec 06, 2011 3:49 am
 Reply with quote
Hi Alissa,

shouldn't there be 2 output files and File2 is a VB file


Gerry
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 496
Location: USA
PostPosted: Tue Dec 06, 2011 4:19 am
 Reply with quote
My oversight, thought both were FB and writing to the same output file. Must have read the post too quickly. I will work on the proper resolution. thanks for bringing this to my attention.
Back to top
View user's profile Send private message
xknight

Active User


Joined: 22 Jan 2008
Posts: 117
Location: Liberty city
PostPosted: Tue Dec 06, 2011 12:34 pm
 Reply with quote
Hello Jagan,

Assuming you have LATEST PTF version to support synctool,

below are the snippet try and let us know

Code:
//STEP01   EXEC PGM=IEBGENER                                   
//SYSPRINT DD SYSOUT=*                                         
//SYSIN    DD *                                                 
 GENERATE MAXFLDS=1                                             
 RECORD FIELD=(2200,1,,1)                                       
//SYSUT1   DD DSN=W635968.B.TEST.INPUT.VB,DISP=SHR             
//SYSUT2   DD DISP=(MOD,PASS,DELETE),UNIT=SYSDA,               
//            RECFM=FB,LRECL=2200,BLKSIZE=22000,               
//            SPACE=(CYL,(10,10),RLSE),                         
//            DSN=&TMP1                                         
//*                                                             
//STEP02 EXEC PGM=ICETOOL                                               
//IN1    DD DSN=W635968.B.TEST.INPUT.FB,DISP=SHR                       
//TMP1   DD DSN=&TMP1,DISP=SHR                                         
//TMP2   DD DSN=&TMP2,                                                 
//          DISP=(MOD,PASS,DELETE),                                     
//          RECFM=FB,LRECL=2201,BLKSIZE=0,                             
//          SPACE=(CYL,(50,50),RLSE),UNIT=SYSDA                         
//TMP3   DD DSN=&TMP3,                                                 
//          DISP=(MOD,PASS,DELETE),                                     
//          RECFM=FB,LRECL=2201,BLKSIZE=0,                             
//           SPACE=(CYL,(50,50),RLSE),UNIT=SYSDA                       
//TMP4   DD DSN=&TMP4,                                                 
//          DISP=(MOD,PASS,DELETE),                                     
//          RECFM=FB,LRECL=2201,BLKSIZE=0,                             
//           SPACE=(CYL,(50,50),RLSE),UNIT=SYSDA                       
//OUT1   DD SYSOUT=*                                             
//OUT2   DD SYSOUT=*                                             
//DFSMSG DD SYSOUT=*                                             
//TOOLMSG DD SYSOUT=*                                           
//TOOLIN DD *                                                   
  COPY FROM(TMP1) TO(TMP2) USING(CTL1)                           
  COPY FROM(IN1) TO(TMP2)                                                                             
  SELECT FROM(TMP2) TO(TMP3) ON(1,12,CH) NODUPS                 
  SELECT FROM(TMP2) TO(TMP4) ON(1,12,CH) ALLDUPS                 
  COPY FROM(TMP3) TO(OUT1) USING(CTL2)                           
  COPY FROM(TMP4) TO(OUT2) USING(CTL3)                           
/*                                                               
//CTL1CNTL DD *                                                 
  OUTREC OVERLAY=(2201:C'2')                                     
/*                                                               
//CTL2CNTL DD *                                                 
  INCLUDE COND=(2201,1,CH,NE,C'2')                               
  OUTREC BUILD=(1,2200)                                         
/*                                                               
//CTL3CNTL DD *                                       
  INCLUDE COND=(2201,1,CH,EQ,C'2')                   
  OUTREC BUILD=(1,2200)                               
/*


You need to do,

Out1 - Change the output filename for Unmatched records from file 1
Out2 - Change the output filename for matched records from file 2

Note: It is an alter solution of Joins, if you need to reduce the passes, you can use the joins in two steps.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts FTP VB File from Mainframe retaining ... JCL & VSAM 8
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us