IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Finding the differences bw two files
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
revdpoel

New User


Joined: 01 Nov 2006
Posts: 56
PostPosted: Wed Nov 19, 2008 1:57 am
 Reply with quote
I have the next problem.
Two files (which are unloads from a DB2-table) have to be matched. I will simplify the problem to make it more understandable
Both files are FB 80.
Both files contain a customer-id (numeric 5) and a customer-name (char 75). They are sorted on customer-id. There is only one record per customer-id.

File A (unload source table, leading table)

00223JA Williams
01234BB Johnson
04343AB Obama
45989General Johnson

File B (unload target table)

01234BB Johnson
04343AB Obamaaaaaaaa
12129John McBain
45989General Johnson

In file A the record from file B with customer-id 12129 is not available. We call this a delete (from the target table).
In file B the record from file A with customer-id 00223 is not available. We call this an insert (into the target table)
Customer-id 04343 is available in both files and the name is changed. We call this an update (in the target table)
Customer-id’s 01234 and 45989 are available in both files and unchanged.

I would like to have two solution.
Solution 1 where 4 files are created: file 1 with the deletes, 2 with the inserts, 3 with the updates and 4 with the unchanged.

File 1 (deletes)
12129John McBain

File 2 (inserts)
00223JA Williams

File 3 (updates)
04343AB Obama

File 4 (unchanged)
01234BB Johnson
45989General Johnson


Solution 2 where 1 file is created. In this file the records records get a mutation-indicator in the first position. D = delete, I = insert, U = update, N = Not changed

File
I00223JA Williams
N01234BB Johnson
U04343AB Obama
D12129John McBain
N45989General Johnson

Who can help me?
Thank you very much
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Wed Nov 19, 2008 4:53 am
 Reply with quote
revdpoel,

The following DFSORT/ICETOOL will give you the desired results. You can do this with the new WHEN=GROUP function of DFSORT available with z/OS DFSORT V1R5 PTF UK90013 (July, 2008) like this:

To differentiate the 2 files from each we add a dummy record before each file which has a string HEADER and we use that to propagate it to every record in the file that follows it

Code:

//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*     
//DFSMSG   DD SYSOUT=*     
//IN       DD * 
HEADER-11               
//         DD *         
00223JA WILLIAMS         
01234BB JOHNSON         
04343AB OBAMA           
45989GENERAL JOHNSON     
//         DD *         
HEADER-22               
//         DD *         
01234BB JOHNSON         
04343AB OBAMAAAAAAAA     
12129JOHN MCBAIN         
45989GENERAL JOHNSON
//*       
//DELETE   DD SYSOUT=*                                                 
//INSERT   DD SYSOUT=*                                                 
//UPDATE   DD SYSOUT=*                                                 
//UNCHNG   DD SYSOUT=*
//TOOLIN   DD *                                                       
  SPLICE FROM(IN) TO(OUT) ON(1,5,CH) WITH(82,76) KEEPNODUPS USING(CTL1)
//CTL1CNTL DD *                                                       
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,6,CH,EQ,C'HEADER'),PUSH=(81:8,2)),
  IFTHEN=(WHEN=(1,6,CH,EQ,C'HEADER'),OVERLAY=(160X)),                 
  IFTHEN=(WHEN=(81,2,ZD,EQ,11),BUILD=(1,82,75X)),                     
  IFTHEN=(WHEN=(81,2,ZD,EQ,22),BUILD=(1,5,81:81,2,83:6,75))           
                                                                       
  OUTFIL FNAMES=DELETE,BUILD=(1,5,83,75),INCLUDE=(81,2,ZD,EQ,22)       
                                                                       
  OUTFIL FNAMES=INSERT,BUILD=(1,80),INCLUDE=(81,2,ZD,EQ,11)           
                                                                       
  OUTFIL FNAMES=UPDATE,BUILD=(1,80),                                   
  INCLUDE=(81,2,ZD,EQ,12,AND,(6,75,CH,NE,83,75,CH))                   
                                                                       
  OUTFIL FNAMES=UNCHNG,BUILD=(1,80),                                   
  INCLUDE=(81,2,ZD,EQ,12,AND,(6,75,CH,EQ,83,75,CH))                   
//*                                                                   


The output from this job is

Delete file
Code:

12129JOHN MCBAIN


Insert file
Code:

00223JA WILLIAMS


Update file
Code:

04343AB OBAMA


Unchng file
Code:

01234BB JOHNSON     
45989GENERAL JOHNSON


If you don't have the July, 2008 PTF installed, ask your System Programmer to install it (it's free).

For complete details on the new WHEN=GROUP and the other new functions available with PTF UK90013, see:

Use [URL] BBCode for External Links

Hope this helps...

Cheers
Back to top
View user's profile Send private message
revdpoel

New User


Joined: 01 Nov 2006
Posts: 56
PostPosted: Wed Nov 19, 2008 11:47 pm
 Reply with quote
Thank you very much

do you also know a solution for the second question?

Solution 2 where 1 file is created. In this file the records records get a mutation-indicator in the first position. D = delete, I = insert, U = update, N = Not changed

File
I00223JA Williams
N01234BB Johnson
U04343AB Obama
D12129John McBain
N45989General Johnson

thanks in advance
Ron
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Thu Nov 20, 2008 12:40 am
 Reply with quote
revdpoel,

oops did not see that you asked for 2 solutions.For the second requirement, use the following DFSORT/ICETOOL job. The output lrecl is increased by a byte to accomadate the indicator in pos 1

Code:

//STEP0200 EXEC PGM=ICETOOL     
//TOOLMSG  DD SYSOUT=*           
//DFSMSG   DD SYSOUT=*           
//IN       DD *                 
HEADER-11                       
//         DD *                 
00223JA WILLIAMS                 
01234BB JOHNSON                 
04343AB OBAMA                   
45989GENERAL JOHNSON             
//         DD *                 
HEADER-22                       
//         DD *                 
01234BB JOHNSON                 
04343AB OBAMAAAAAAAA             
12129JOHN MCBAIN                 
45989GENERAL JOHNSON             
//OUT      DD SYSOUT=*           
//TOOLIN   DD *                                                       
  SPLICE FROM(IN) TO(OUT) ON(1,5,CH) WITH(82,76) KEEPNODUPS USING(CTL1)
//CTL1CNTL DD *                                                       
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,6,CH,EQ,C'HEADER'),PUSH=(81:8,2)),
  IFTHEN=(WHEN=(1,6,CH,EQ,C'HEADER'),OVERLAY=(160X)),                 
  IFTHEN=(WHEN=(81,2,ZD,EQ,11),BUILD=(1,82,75X)),                     
  IFTHEN=(WHEN=(81,2,ZD,EQ,22),BUILD=(1,5,81:81,2,83:6,75))           
                                                                       
  OUTFIL FNAMES=OUT,IFOUTLEN=81,OMIT=(1,80,CH,EQ,C' '),               
  IFTHEN=(WHEN=(81,2,ZD,EQ,11),BUILD=(C'I',1,80)),                     
  IFTHEN=(WHEN=(81,2,ZD,EQ,22),BUILD=(C'D',1,5,83,75)),               
  IFTHEN=(WHEN=(81,2,ZD,EQ,12,AND,(6,75,CH,NE,83,75,CH)),             
  BUILD=(C'U',1,80)),                                                 
  IFTHEN=(WHEN=(81,2,ZD,EQ,12,AND,(6,75,CH,EQ,83,75,CH)),             
  BUILD=(C'N',1,80))                                                   
//*
Back to top
View user's profile Send private message
revdpoel

New User


Joined: 01 Nov 2006
Posts: 56
PostPosted: Thu Nov 20, 2008 3:25 am
 Reply with quote
really really thanks
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts Finding and researching jobs All Other Mainframe Topics 0
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us