Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 2285
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 FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm
No new posts Split 1 file into 10 output Files - S... Prasanth Kumar SYNCSORT 5 Sat Sep 16, 2017 12:02 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
No new posts Match two files using multiple keys a... santoshks1987 SYNCSORT 11 Fri Aug 18, 2017 10:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us