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
 

 

JOIN 2 files with duplicates using SYNCSORT

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

New User


Joined: 10 Jan 2011
Posts: 6
Location: Chennai

PostPosted: Tue Jan 11, 2011 4:54 pm    Post subject: JOIN 2 files with duplicates using SYNCSORT
Reply with quote

Hi,
I need to compare 2 files on first 5 bytes and get the matching records, both has duplicates in the key.
I don't want a cartesian product in the output. I need to do one to one matching. Since i use SYNCSORT i am unable to use NOSEQCK . Could some one help me accomplish this using SYNCSORT.
Basically I need 3 bytes starting from Position 6 of the second file to be replaced with 3 bytes from position 6 of the first file.

Sample Input File1: RECFM: FB, LRECL: 80
Code:

XXXXX999CHENNAI...
XXXXX123SALEM....
YYYYY445MUMBAI...
YYYYY678DELHI...


Sample Input file2: RECFM: FB, LRECL: 80

Code:

XXXXX001BANGLORE.....
XXXXX657SURAT.....
YYYYY978SIMLA....
ZZZZZ000HYDERABAD.....


Expected Output: RECFM: FB, LRECL: 80

Code:

XXXXX999BANGLORE....
XXXXX123SURAT....
YYYYY445SIMLA....


Please find the sort card i used
Code:

JOINKEYS FILE=F1,FIELDS=(1,5,A),SORTED,NOSEQCK     
JOINKEYS FILE=F2,FIELDS=(1,5,A),SORTED,NOSEQCK     
REFORMAT FIELDS=(F2:1,80,F1:6,3)                   
OUTREC FIELDS=(1,5,81,3,9,71)                       
SORT FIELDS=COPY                                   


This is the error message i got:
Code:

 SYNCSORT FOR Z/OS  1.3.2.0R    U.S. PATENTS: 4210961, 5117495   (C) 2007 SYNCSO
                                                       z/OS   1.11.0           
 SYNCSORT LICENSED FOR CPU SERIAL NUMBER 16C35, MODEL 2097 708             LICEN
 SYSIN :                                                                       
   JOINKEYS FILE=F1,FIELDS=(1,5,A),SORTED,NOSEQCK                               
                                         *                                     
   JOINKEYS FILE=F2,FIELDS=(1,5,A),SORTED,NOSEQCK                               
                                         *                                     
   REFORMAT FIELDS=(F2:1,80,F1:6,3)                                             
   OUTREC FIELDS=(1,5,81,3,9,71)                                               
   SORT FIELDS=COPY                                                             
 WER268A  JOINKEYS STATEMENT: SYNTAX ERROR                                     
 WER268A  JOINKEYS STATEMENT: SYNTAX ERROR                                     
 WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                                 
 WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                                 
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Tue Jan 11, 2011 6:42 pm    Post subject:
Reply with quote

WIll always the second file have lesser number of duplicates? Show an example input and the expected output when there are,
1) 3 duplicate keys in file-2 and you have 2 duplicates in file-1
2) 3 duplicate keys in file-2 and no duplicates in file-1
3) 3 duplicate keys in file-2 and no key present in file-1

btw NOSEQCK is a DFSORT option, you may not be able to use it in Syncsort.
Back to top
View user's profile Send private message
Gururajan Balaji

New User


Joined: 10 Jan 2011
Posts: 6
Location: Chennai

PostPosted: Tue Jan 11, 2011 7:31 pm    Post subject:
Reply with quote

Hi Arun,
All the 3 scenarios mentioned by you are possible. In any case we need only matching records. So we can drop a record which does not have any match in the other file.
Here is the input and output format for the scenarios you have mentioned.

Input file1:
Code:

XXXXX999CHENNAI...
XXXXX123SALEM....
YYYYY445MUMBAI...
YYYYY678DELHI...
1YYYY000KOLKATA...
1YYYY001COCHIN...
2YYYY111DELHI...
3YYYY112MYSORE...



Input file 2:

Code:

XXXXX001BANGLORE.....
XXXXX657SURAT.....
YYYYY978SIMLA....
ZZZZZ000HYDERABAD.....
1YYYY010SIMLA...
1YYYY020BENGAL...
1YYYY030MANGLORE...
2YYYY199MEGALAYA...
2YYYY211ASSAM...
2YYYY399TRIPURA...



Expected Output:

Code:

XXXXX999BANGLORE....
XXXXX123SURAT....
YYYYY445SIMLA....
1YYYY000SIMLA...
1YYYY001BENGAL...
1YYYY030MANGLORE...
2YYYY111MEGALAYA...
2YYYY211ASSAM...
2YYYY399TRIPURA...
 
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 Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm


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