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 : Matching and Non Matching records

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
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    Post subject: SYNCSORT : Matching and Non Matching records
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    Post subject:
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

Senior Member


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

PostPosted: Wed Aug 01, 2007 12:25 pm    Post subject:
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    Post subject: Sync sort : matching and non matching in single step
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

Senior Member


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

PostPosted: Mon Aug 06, 2007 4:16 pm    Post subject: Re: Sync sort : matching and non matching in single step
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    Post subject:
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    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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm


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