Joined: 13 Apr 2011 Posts: 4 Location: United Kingdom
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).
//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?
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