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
 

 

compare two files on two keys

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 113
Location: India

PostPosted: Wed Jul 08, 2009 3:23 pm    Post subject: compare two files on two keys
Reply with quote

Hi,

I want to compare two files on two keys. Prior to this i posted the same topic on one key comparison and skolusu gave solution for that which is working well. But there is a change in my requirement. I want to compare two files on two keys. I tried to modify skolusu solution of one key comparison but not able to get output. Please help.

The link for previous toipc which is on one key comparison is
http://www.ibmmainframes.com/viewtopic.php?t=41548&start=15

LRECL=31, RECFM=FB for all input and output files.
KEY1=1,5,ZD
KEY2=23,3,CH

Code:

----+----1----+----2----+----3----+----4----+----5
//FILE1    DD *                               
11111 BHASKAR 2000.00 HYD INDIA  <-- TO OUTPUT ONLYF1
11111 BHASKAR 3000.00 HYD INDIA  <-- TO OUTPUT ONLYF1
22222 SUNIL   1000.00 HYD INDIA  <-- TO OUTPUT ONLYF1
33333 KUMAR   2500.00 VIJ INDIA  <-- TO OUTPUT MATCH
33333 KUMAR   2500.00 VIJ INDIA  <-- TO OUTPUT MATCH
/*                                           
//FILE2    DD *                               
33333 KUMAR   300.00  VIJ INDIA  <-- TO OUTPUT MATCH
33333 KUMAR   5000.00 VIJ INDIA  <-- TO OUTPUT MATCH
44444 KTBS    1300.00 VIR US     <-- TO OUTPUT ONLYF2
55555 BACHI   1110.00 BGL INDIA  <-- TO OUTPUT ONLYF2
55555 BACHI   1000.00 BGL INDIA  <-- TO OUTPUT ONLYF2
/*                                           


Both input files will have duplicates.
Two records of 11111 and one record of 22222 from file1 which doesnt had match on two keys in file2 should be written to output ONLYF1 file.
One record of 44444 and two records of 55555 from file2 which doesnt had match on two keys in file1 should be written to output ONLYF2 file.
Two records of 33333 from file1 which had match on two keys with two records of 33333 from file2 should be written to output MATCH file. ie. totally all 4 records should be written to MATCH output file.
Back to top
View user's profile Send private message

Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Jul 08, 2009 8:42 pm    Post subject: Reply to: compare two files on two keys
Reply with quote

bhaskar_kanteti,

The following DFSORT/ICETOOL JCL will give you the desired results


Code:

//STEP0100 EXEC PGM=ICETOOL                                   
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//IN1      DD *                                               
11111 BHASKAR 2000.00 HYD INDIA  <-- TO OUTPUT ONLYF1         
11111 BHASKAR 3000.00 HYD INDIA  <-- TO OUTPUT ONLYF1         
22222 SUNIL   1000.00 HYD INDIA  <-- TO OUTPUT ONLYF1         
33333 KUMAR   2500.00 VIJ INDIA  <-- TO OUTPUT MATCH           
33333 KUMAR   2500.00 VIJ INDIA  <-- TO OUTPUT MATCH           
//IN2      DD *                                               
33333 KUMAR   300.00  VIJ INDIA  <-- TO OUTPUT MATCH           
33333 KUMAR   5000.00 VIJ INDIA  <-- TO OUTPUT MATCH           
44444 KTBS    1300.00 VIR US     <-- TO OUTPUT ONLYF2         
55555 BACHI   1110.00 BGL INDIA  <-- TO OUTPUT ONLYF2         
55555 BACHI   1000.00 BGL INDIA  <-- TO OUTPUT ONLYF2         
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(1,1),RLSE) 
//MATCH    DD SYSOUT=*                                         
//ONLYF1   DD SYSOUT=*                                         
//ONLYF2   DD SYSOUT=*                                         
//TOOLIN   DD *                                               
  SORT FROM(IN1) USING(CTL1)                                   
  SORT FROM(IN2) USING(CTL2)                                   
  SPLICE FROM(T1) ON(1,5,CH) ON(23,3,CH) TO(MATCH) -           
  WITHALL WITH(1,41) KEEPNODUPS USING(CTL3)                   
//CTL1CNTL DD *                                                 
  SORT FIELDS=(1,5,CH,A,23,3,CH,A)                             
  OUTREC OVERLAY=(32:1,5,23,3,SEQNUM,8,ZD,RESTART=(32,8))       
  OUTFIL FNAMES=T1,REMOVECC,BUILD=(1,31,C'A',40,8,C'11'),       
  SECTIONS=(1,5,23,3,TRAILER3=(1,5,23:23,3,32:X,8C'0',C'11'))   
//CTL2CNTL DD *                                                 
  SORT FIELDS=(1,5,CH,A,23,3,CH,A)                             
  OUTREC OVERLAY=(32:1,5,23,3,SEQNUM,8,ZD,RESTART=(32,8))       
  OUTFIL FNAMES=T1,REMOVECC,BUILD=(1,31,C'B',40,8,C'22'),       
  SECTIONS=(1,5,23,3,TRAILER3=(1,5,23:23,3,32:X,8C'0',C'22'))   
//CTL3CNTL DD *                                                 
  SORT FIELDS=(1,5,CH,A,23,3,CH,A,32,9,CH,A)                   
  SUM FIELDS=(42,1,ZD)                                         
  OUTFIL FNAMES=MATCH,BUILD=(1,31),INCLUDE=(41,2,SS,EQ,C'13,23')
  OUTFIL FNAMES=ONLYF1,BUILD=(1,31),INCLUDE=(41,2,ZD,EQ,11)     
  OUTFIL FNAMES=ONLYF2,BUILD=(1,31),INCLUDE=(41,2,ZD,EQ,22)     
/*


If you are getting an error on sections statement then change just the sections statement to the following

Code:

SECTIONS=(1,5,SKIP=0L,23,3,SKIP=0L,
Back to top
View user's profile Send private message
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 113
Location: India

PostPosted: Thu Jul 09, 2009 12:13 pm    Post subject: Reply to: compare two files on two keys
Reply with quote

Hi Skolusu,

Thank you so much. Its working perfectly. Excellent solution.

Can you please explain me the logic. Especially i didnt understand the sections part and sum fields part.

Sections part:
Code:
SECTIONS=(1,5,SKIP=0L,23,3,SKIP=0L,               
          TRAILER3=(1,5,23:23,3,32:X,8C'0',C'11'))


Sum fields part:
Code:
SUM FIELDS=(42,1,ZD)
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Jul 09, 2009 9:16 pm    Post subject: Reply to: compare two files on two keys
Reply with quote

bhaskar_kanteti,

The logic is quite simple. We create an 1 additional records for every key combo from 2 files using sections and trailer3 parms and we will use that to sum to find if we have a match or not.

Sections is a control break of the key change and whenever the key changes we create a record with just the keys and space + init zero's and file ind.

This is how file1 will be formatted after first sort

Code:

--------------------------------------------------               
KEY1 | DATA1         |KEY2|DATA2|IND-SEQ  |FILEIND               
1-5  | 6-22          |2325|26-31|32-40    |41-42                 
--------------------------------------------------               
11111 BHASKAR 2000.00 HYD  INDIA A00000001 11                   
11111 BHASKAR 3000.00 HYD  INDIA A00000002 11                   
11111                 HYD         00000000 11  < SECTIONS RECORD
22222 SUNIL   1000.00 HYD  INDIA A00000001 11                   
22222                 HYD         00000000 11  < SECTIONS RECORD
33333 KUMAR   2500.00 VIJ  INDIA A00000001 11                   
33333 KUMAR   2500.00 VIJ  INDIA A00000002 11                   
33333                 VIJ         00000000 11  < SECTIONS RECORD


This is how file2 will be formatted after second sort

Code:

--------------------------------------------------               
KEY1 | DATA1         |KEY2|DATA2|IND-SEQ  |FILEIND               
1-5  | 6-22          |2325|26-31|32-40    |41-42                 
-------------------------------------------------- 
33333 KUMAR   300.00  VIJ  INDIA B00000001 22                   
33333 KUMAR   5000.00 VIJ  INDIA B00000002 22                   
33333                 VIJ         00000000 22  < SECTIONS RECORD
44444 KTBS    1300.00 VIR  US    B00000001 22                   
44444                 VIR         00000000 22  < SECTIONS RECORD
55555 BACHI   1110.00 BGL  INDIA B00000001 22                   
55555 BACHI   1000.00 BGL  INDIA B00000002 22                   
55555                 BGL         00000000 22  < SECTIONS RECORD


Now before splice is done we sort on

key1 + key2 + ind-seq

and then sum on 1 byte file indicator at pos 42

This is how the input to splice would look like

Code:

--------------------------------------------------           
KEY1 | DATA1         |KEY2|DATA2|IND-SEQ  |FILEIND           
1-5  | 6-22          |2325|26-31|32-40    |41-42             
--------------------------------------------------           
11111                 HYD         00000000 11 < SUMMED RECORD
11111 BHASKAR 2000.00 HYD  INDIA A00000001 11                 
11111 BHASKAR 3000.00 HYD  INDIA A00000002 11                 
22222                 HYD         00000000 11 < SUMMED RECORD
22222 SUNIL   1000.00 HYD  INDIA A00000001 11                 
33333                 VIJ         00000000 13 < SUMMED RECORD
33333 KUMAR   2500.00 VIJ  INDIA A00000001 11                 
33333 KUMAR   2500.00 VIJ  INDIA A00000002 11                 
33333 KUMAR   300.00  VIJ  INDIA B00000001 22                 
33333 KUMAR   5000.00 VIJ  INDIA B00000002 22                 
44444                 VIR         00000000 22 < SUMMED RECORD
44444 KTBS    1300.00 VIR  US    B00000001 22                 
55555                 BGL         00000000 22 < SUMMED RECORD
55555 BACHI   1110.00 BGL  INDIA B00000001 22                 
55555 BACHI   1000.00 BGL  INDIA B00000002 22                 


Key 11111, 22222 does not have a match in file2 and hence the sum is only 1 key 33333 has a match on both files and hence the sum is 3

Key 44444, 55555 does not have a match in file1 and hence the sum is only 2

We now splice that summed value on to all the records for each key combo and this is how the output will be before OUTFIL processing

Code:

--------------------------------------------------
KEY1 | DATA1         |KEY2|DATA2|IND-SEQ  |FILEIND
1-5  | 6-22          |2325|26-31|32-40    |41-42   
--------------------------------------------------
11111 BHASKAR 2000.00 HYD  INDIA A00000001 11     
11111 BHASKAR 3000.00 HYD  INDIA A00000002 11     
22222 SUNIL   1000.00 HYD  INDIA A00000001 11     
33333 KUMAR   2500.00 VIJ  INDIA A00000001 13     
33333 KUMAR   2500.00 VIJ  INDIA A00000002 13     
33333 KUMAR   300.00  VIJ  INDIA B00000001 23     
33333 KUMAR   5000.00 VIJ  INDIA B00000002 23     
44444 KTBS    1300.00 VIR  US    B00000001 22     
55555 BACHI   1110.00 BGL  INDIA B00000001 22     
55555 BACHI   1000.00 BGL  INDIA B00000002 22     


Now using OUTFIL we pick the records we want and chop off the additional bytes we added. All the matching records will have either a 13 or 23 and unmatched records will have the respective file ind

phew coding the solution was much much easier than trying to explain it
Back to top
View user's profile Send private message
bhaskar_kanteti

Active User


Joined: 01 Feb 2007
Posts: 113
Location: India

PostPosted: Thu Jul 09, 2009 10:19 pm    Post subject: Reply to: compare two files on two keys
Reply with quote

Hi Skolusu,

Thank you so much. You explained step by step very clearly and that too with screen shots. Thanks for your excellent logic and thanks for your detailed explanation.
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am


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