Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Syncsort two-file key match

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
jaganmoni1

New User


Joined: 31 Oct 2008
Posts: 33
Location: CANADA

PostPosted: Tue Nov 29, 2011 2:41 am    Post subject: Syncsort two-file key match
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: 500
Location: USA

PostPosted: Tue Dec 06, 2011 3:21 am    Post subject:
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: 1703
Location: Australia

PostPosted: Tue Dec 06, 2011 3:49 am    Post subject:
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: 500
Location: USA

PostPosted: Tue Dec 06, 2011 4:19 am    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Extra character appears in file when ... Balu5491 All Other Mainframe Topics 1 Wed Jul 26, 2017 2:39 pm
No new posts SSH - known_hosts file configuration vasanthz All Other Mainframe Topics 2 Wed Jul 26, 2017 2:10 am
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us