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

SYNCSORT : Matching and Non Matching records


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
chinnarajr

New User


Joined: 01 Jun 2007
Posts: 16
Location: Chennai

PostPosted: Tue Jul 31, 2007 7:27 pm
Reply with quote

hi,

i have the following two files.

FILE1
-------
11111
22222
33333
44444
55555

FILE2
-------
22222
33333
55555
77777
88888
99999

i need to extract the matching records and non matching records from FILE1 & FILE2 using SYNCSORT.

So output file FILE3 should have Non - Matching records of FILE1,that is
11111
44444

output file FILE4 should have Matching Records, that is
22222
33333
55555

i need the JCl for this with the file length specification.

Please reply me as soon as possible.

thanks
Chinna
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Tue Jul 31, 2007 10:14 pm
Reply with quote

Look in the manual for the JOIN syntax. The output files will pickup the file length definition from the input files (assuming they have the same record length)

Code:

//STEP01S  EXEC PGM=SORT
//SORTJNF1 DD DSN=IN.FILE1.FILENAME,DISP=SHR
//SORTJNF2 DD DSN=IN.FILE2.FILENAME,DISP=SHR
//SORTOF1  DD DSN=OUT.FILE3.FILENAME,     
//         DISP=(MOD,CATLG,DELETE),                 
//         SPACE=(CYL,(50,100),RLSE)         
//SORTOF2  DD DSN=OUT.FILE4.FILENAME,     
//         DISP=(MOD,CATLG,DELETE),                 
//         SPACE=(CYL,(50,100),RLSE)         
//SYSOUT   DD SYSOUT=X                               
//SYSIN    DD   *                                   
    JOINKEYS FILE=F1,FIELDS(1,5,A)                 
    JOINKEYS FILE=F2,FIELDS(1,5,A)                 
    SORT     FIELDS=COPY                             
    JOIN     *******UNPAIRED,F1,ONLY*******

You need some other stuff in here to define your output files and you will need to change the line with ******* wrapped around it *******. As I show it, it will output the unmatched from FILE1 if you change SORTOF1 to SORTOUT and remove SORTOF2. I believe both files can be generated in one sort step, check the SYNCSORT manual to see or the Exploting Syncsort Join manual.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Wed Aug 01, 2007 12:25 pm
Reply with quote

Hi,

Try below JCL, input files are of LRECL=80 & key length is 3.
By the way when you say
chinnarajr wrote:
..and non matching records from FILE1 & FILE2 using SYNCSORT

two conditions arises, which eventually give you two outputs:
Output1= Non-matching records only in in-file1 not in in-file2
Output2= Non-matching records only in in-file2 not in in-file1

Code:
//STEP001 EXEC PGM=SYNCTOOL                                 
//TOOLMSG DD SYSOUT=*                                       
//DFSMSG  DD SYSOUT=*                                       
//IN1     DD DSN=HLQ.FIRST.FILE,DISP=SHR                   
//IN2     DD DSN=HLQ.SECOND.FILE,DISP=SHR                   
//T1      DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),         
//           DISP=(MOD,PASS)                               
//OUT12   DD DSN=HLQ.MATCHED.OUTPUT,DISP=(NEW,CATLG,DELETE)
//OUT1    DD DSN=HLQ.ONLY.IN.IN1.OUT,DISP=(NEW,CATLG,DELETE)
//OUT2    DD DSN=HLQ.ONLY.IN.IN2.OUT,DISP=(NEW,CATLG,DELETE)
//TOOLIN  DD *                                             
  COPY FROM(IN1) TO(T1) USING(CTL1)                         
  COPY FROM(IN2) TO(T1) USING(CTL2)                         
  SPLICE FROM(T1) TO(OUT12) ON(1,3,CH) WITH(81,1) -         
  USING(CTL3) KEEPNODUPS                                   
/*                                                         
//CTL1CNTL DD *                                             
  INREC OVERLAY=(80:C'11')                                 
/*                                                         
//CTL2CNTL DD *                                             
//CTL2CNTL DD *                                               
  INREC OVERLAY=(80:C'22')                                   
/*                                                           
//CTL3CNTL DD *                                               
  OUTFIL FNAMES=OUT12,INCLUDE=(80,2,CH,EQ,C'12'),BUILD=(1,80)
  OUTFIL FNAMES=OUT1,INCLUDE=(80,2,CH,EQ,C'11'),BUILD=(1,80) 
  OUTFIL FNAMES=OUT2,INCLUDE=(80,2,CH,EQ,C'22'),BUILD=(1,80) 
/*                                                           


Hope this helps.
Back to top
View user's profile Send private message
chinnarajr

New User


Joined: 01 Jun 2007
Posts: 16
Location: Chennai

PostPosted: Wed Aug 01, 2007 1:35 pm
Reply with quote

Hi Stodolas / Anuj

According to my query,

The FILE3 should have Non matching records ( that is those records from FILE1, which is not there in FILE2 )

The FILE4 should have Matching records ( that is records which are common to both FILE1 & FILE2)

I need the above functionality in a SINGLE step of a JCL using SYNCSORT.

Note : The Record Length : 425
The key part length : 31

Thanks,
Chinna
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Mon Aug 06, 2007 4:16 pm
Reply with quote

chinnarajr wrote:
I need the above functionality in a SINGLE step of a JCL using SYNCSORT.
I think, JCL given uses only one step, //STEP001. icon_confused.gif

Quote:
The FILE3 should have Non matching records ( that is those records from FILE1, which is not there in FILE2 )
Remove code for OUT2 from the JCL.

Hope this helps.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Mon Aug 06, 2007 4:41 pm
Reply with quote

chinnarajr
Quote:
The FILE3 should have Non matching records ( that is those records from FILE1, which is not there in FILE2 )

The OUT1 file in Anuj's JCL is your FILE3
Quote:
The FILE4 should have Matching records ( that is records which are common to both FILE1 & FILE2)
OUT12 is your FILE4

And just for your information,OUT2 contains the records which were there in FILE2 but not in FILE1.
So if you dont need OUT2 then just remove it.

Quote:
I need the above functionality in a SINGLE step of a JCL using SYNCSORT.
SYNCTOOL is also a product of SYNCSORT.
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 only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts EZT program to build a flat file with... All Other Mainframe Topics 9
No new posts JCL sortcard to print only the records DFSORT/ICETOOL 11
Search our Forums:

Back to Top