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
 
Finding the differences bw two files

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Finding the differences bw two files
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    Post subject: Reply to: Finding the differences bw two files
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:

www.ibm.com/systems/support/storage/software/sort/mvs/ugpf/

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    Post subject:
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    Post subject: Reply to: Finding the differences bw two files
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    Post subject:
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    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 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 LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 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

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