View previous topic :: View next topic
Author
Message
chinnarajr New User Joined: 01 Jun 2007Posts: 16 Location: Chennai
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
stodolas Active Member Joined: 13 Jun 2007Posts: 632 Location: Wisconsin
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
Anuj Dhawan Superior Member Joined: 22 Apr 2006Posts: 6250 Location: Mumbai, India
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
chinnarajr New User Joined: 01 Jun 2007Posts: 16 Location: Chennai
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
Anuj Dhawan Superior Member Joined: 22 Apr 2006Posts: 6250 Location: Mumbai, India
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.
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
krisprems Active Member Joined: 27 Nov 2006Posts: 649 Location: India
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
Please enable JavaScript!