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

COMPARE TWO FILES TO CREATE DELTA FILE


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Brad Eichbauer

New User


Joined: 13 Apr 2011
Posts: 4
Location: United Kingdom

PostPosted: Thu Apr 14, 2011 12:39 pm
Reply with quote

Hi

I have a requirement to create a delta file by comparing a current version of a file with a previous version of a file. The resultant file must include a header, a trailer and the records must have an update indicator - 'I' for new records (on the current version but not the previous), 'D' for deleted records (on the previous version but not the current), and 'C' for records that have changed (on both the current and previous but different). Furthermore the output must be unpacked ie. binary/packed decimal fields converted into numeric display fields.

The two input files are shown below (records are displayed in hex format).

IN1 (6 records)
Code:

----+----1----+----2
12345678901234567890

39C00A8CFFC00011F444
BAA109A70110011C1000

39C00518FFC22222F444
BAA2135E0212222D2000

39C00FA5FFC00033F444
BAA31C050340033C3000

39C00A21FFC00044F444
BAA426BC0610044C4000

39C004BEFFC00055F444
BAA53F530510055D5000

39C00AC7FFC00077F444
BAA742B10710077D7000



IN2 (5 records)

Code:

----+----1----+----2
12345678901234567890


39C00A8CFFC00011F444
BAA109A70110011C1000

39C00518FFC00022F444
BAA2135E0210022D2000

39C00FA5FFC00033F444
BAA31C050340033C3000

39C00A21FFC00044F444
BAA426BC0410044C4000

39C00453FFC00044F444
BAA85C680810044C8000


The layout of the above two files:

Code:

10 KEY1                       PIC S9(9) USAGE COMP.  (4 byte binary)
10 KEY2                       PIC S9(9) USAGE COMP.  (4 byte binary)
10 FIELD1                     PIC X(2).                            (2 byte character)
10 FIELD2                     PIC X(1).                            (1 byte character)
10 FIELD3                     PIC S9(9) COMP-3              (5 byte packed decimal)
10 FIELD4                     PIC X(4).                            (4 byte character)



Below is the ICETOOL:

Code:

//STEP010  EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//* CURRENT FILE (FB,LRECL=20)
//IN1      DD DSN=TTYA.FILE1,DISP=SHR
//* PREVIOUS FILE (FB,LRECL=20)
//IN2      DD DSN=TTYA.FILE2,DISP=SHR
//* OUTPUT FILE (WILL EVENTUALLY BE TEMP FILE)
//OUT1     DD DSN=TTYA.FILE.OUT1,DISP=SHR
//* OUTPUT FILE (FB,LRECL=37)
//OUT2     DD DSN=TTYA.FILE.OUT2,DISP=SHR
//T1       DD DSN=&&T1,UNIT=SYSDA,SPACE=(TRK,(5,5)),
//            DISP=(MOD,PASS),LRECL=22
//TOOLIN   DD *
**** ADD 'CC' IDENTIFIER FOR IN1 RECORDS.
COPY FROM(IN1) TO(T1) USING(CTL1)
**** ADD 'PP' IDENTIFIER FOR IN2 RECORDS.
COPY FROM(IN2) TO(T1) USING(CTL2)
**** SPLICE AND MATCH ON THE WHOLE RECORD AND WRITE RESULTS TO OUTPUT FILE
SPLICE FROM(T1) TO(OUT1) ON(1,20,CH) WITH(21,1) -
  KEEPNODUPS USING(CTL3)
**** WHERE RECORDS HAVE CHANGED, A DELETE RECORD AND A
**** INSERT RECORD IS CREATED FOR THE SAME RECORD. THIS
**** SELECT ON THE KEY OF THE RECORD GETS RID OF THE DELETE
**** (OLD) RECORD
SELECT FROM(OUT1) TO(OUT2) ON(2,8,CH) LAST
USING(CTL4)
/*
//CTL1CNTL DD *
  OUTREC FIELDS=(1,20,21:C'CC')
/*
//CTL2CNTL DD *
  OUTREC FIELDS=(1,20,21:C'PP')
/*
//CTL3CNTL DD *
* DISCARD RECORDS THAT HAVE NOT CHANGED
* WRITE OUT 'D'ELETE RECORDS
* WRITE OUT 'I'NSERT RECORDS
   OUTFIL FNAMES=OUT1,
     OMIT=(21,2,CH,EQ,C'PC'),
     IFTHEN=(WHEN=(21,2,CH,EQ,C'PP'),BUILD=(C'D',1,20)),
     IFTHEN=(WHEN=(21,2,CH,EQ,C'CC'),BUILD=(C'I',1,20))
/*
//CTL4CNTL DD *
* ADD HEADER AND TRAILER AND REFORMAT RECORD
   OUTFIL FNAMES=OUT2,
     REMOVECC,HEADER1=(C'A',C' FILENME ',DATE),
     BUILD=(1,1,2,4,BI,M11,6,4,BI,M11,12,1,13,5,PD,M1,18,5),
     TRAILER1=(C'Z',C' FILENME ',COUNT=(EDIT=(TTTTTTT)))
/*



The resultant output file is shown below.

Code:

A FILENME 04/13/11               
I10000000020022222222A222222222-2
I10000000040044444444A000004444 4
I10000000050055555555A000005555-5
I10000000070077777777A000007777-7
D10000000080088888888A000004444 8
Z FILENME 0000005               


The main problem is that where records have changed (ie. is on the previous and current file but a non-key field differs), a delete record and an insert record is created rather than just a single change record - is it possible to create a single change record?

Also is there a better way of doing what I am trying to do as some of the files compared may contain up to and over a million records?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Apr 14, 2011 4:25 pm
Reply with quote

Quote:
is it possible to create a single change record?

Which one you want in this case with flag C? record from F1 or record from F2?
Back to top
View user's profile Send private message
Brad Eichbauer

New User


Joined: 13 Apr 2011
Posts: 4
Location: United Kingdom

PostPosted: Thu Apr 14, 2011 4:29 pm
Reply with quote

From the current file :

//IN1 DD DSN=TTYA.FILE1,DISP=SHR
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Apr 14, 2011 4:55 pm
Reply with quote

Will there be duplicates of combinition of key1,key2 withing a file?
Back to top
View user's profile Send private message
Brad Eichbauer

New User


Joined: 13 Apr 2011
Posts: 4
Location: United Kingdom

PostPosted: Thu Apr 14, 2011 5:26 pm
Reply with quote

No, there will only be one record for a combined key1 and key2 value.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Apr 14, 2011 5:45 pm
Reply with quote

Hi Brad,
Below is sample sortstep which you can use.
I have used 1,10 as key and 11 to 24 is data
Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//CURR     DD *                                                     
AAAAAAAAAA AAAAAAAAAAAAA   - NO MATCH SHOULD BE IN OUTPUT WITH I     
BBBBBBBBBB NEW DATA HERE   - SAME KEY BUT DATA IS CHANGED           
CCCCCCCCCC BBBBBBBBBBBBE   - SAME KEY AND SAME DATA SO DROP         
//PREV     DD *                                                     
DDDDDDDDDD BBBBBBBBBBBBE   - NO MATCH SHOULD BE IN OUTPUT WITH D     
BBBBBBBBBB OLD DATA HERE   - SAME KEY BUT DATA IS CHANGED           
CCCCCCCCCC BBBBBBBBBBBBE   - SAME KEY AND SAME DATA SO DROP         
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=CURR,FIELDS=(1,10,A)                                   
  JOINKEYS F2=PREV,FIELDS=(1,10,A)                                   
  JOIN UNPAIRED                                                     
  REFORMAT FIELDS=(F1:1,24,?,F2:1,24)                               
  OUTFIL REMOVECC,OMIT=(1,24,CH,EQ,26,24,CH),                       
  IFTHEN=(WHEN=(25,1,CH,EQ,C'1'),BUILD=(C'I',1,24)),                 
  IFTHEN=(WHEN=(25,1,CH,EQ,C'2'),BUILD=(C'D',26,24)),               
  IFTHEN=(WHEN=(NONE),BUILD=(C'C',1,24)),                               
  HEADER1=(C'A',C' FILENME ',DATE),                                     
  TRAILER1=(C'Z',C' FILENME ',COUNT=(EDIT=(TTTTTTT)))                   
/*

Output will be
Code:

A FILENME 04/14/11           
IAAAAAAAAAA AAAAAAAAAAAAA     
CBBBBBBBBBB NEW DATA HERE     
DSBBBBBBBBB BBBBBBBBBBBBE     
Z FILENME 0000003             

Modify as per your need.
Back to top
View user's profile Send private message
Brad Eichbauer

New User


Joined: 13 Apr 2011
Posts: 4
Location: United Kingdom

PostPosted: Thu Apr 14, 2011 5:46 pm
Reply with quote

Thanks. I will try that.
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 How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Access to non cataloged VSAM file JCL & VSAM 18
Search our Forums:

Back to Top