Posted: Fri Aug 12, 2005 10:12 am Post subject: Usage of SPLICE
I would like to compare two VSAM files and get output file only with changed records. Comparision should be done based on Key field.
Changed records should be tagged with '1' for Inserted record, '2' for deleted record and '3' for updated records. We are trying this with SPLICE option.
Input 1: first five bytes is a Key field
12345xdxdxd
09876sesese
Input 2: First five bytes is a Key field
12345wertwe
45678tgtgtg
Desired output:
12345wertwe 3 - Updated record
09876sesese 2 - Deleted record
45678tgtgtg 1 - Inserted record
Can you please suggest us solution in ICETOOL/DFSORT?
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Fri Aug 12, 2005 12:49 pm Post subject:
Here's a DFSORT/ICETOOL job that will do what you asked for. You'll need z/OS DFSORT V1R5 PTF UQ95214 or DFSORT R14 PTF UQ95213 (Dec, 2004) in order to use DFSORT's IFTHEN function. Only DFSORT has this function, so if you don't have DFSORT, you won't be able to use it. If you do have DFSORT, but you don't have the Dec, 2004 PTF, ask your System Programmer to install it (it's free). For complete details on all of the new DFSORT and ICETOOL functions available with the Dec, 2004 PTF, see:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1
//IN2 DD DSN=... input file2
//OUT DD DSN=... output file
//T1 DD DSN=&&T1,DISP=(MOD,PASS),UNIT=SYSDA,SPACE=(TRK,(5,5))
//TOOLIN DD *
* Add 11 identifier for FILE2 records.
COPY FROM(IN2) TO(T1) USING(CTL1) VSAMTYPE(F)
* Add 22 identifier for FILE1 records.
COPY FROM(IN1) TO(T1) USING(CTL2) VSAMTYPE(F)
* SPLICE to match up records and add id as follows:
* 1 = Inserted record
* 2 = Deleted record
* 3 = Updated record
SPLICE FROM(T1) TO(OUT) ON(1,5,CH) WITH(14,1) -
KEEPNODUPS USING(CTL3)
/*
//CTL1CNTL DD *
* Mark FILE1 records with '11'
OUTREC FIELDS=(1,11,13:C'11')
/*
//CTL2CNTL DD *
* Mark FILE2 records with '22'
OUTREC FIELDS=(1,11,13:C'22')
/*
//CTL3CNTL DD *
* Set id.
OUTFIL FNAMES=OUT,IFOUTLEN=13,
IFTHEN=(WHEN=(13,2,CH,EQ,C'12'),BUILD=(1,12,C'3')),
IFTHEN=(WHEN=NONE,BUILD=(1,13))
/*
OUT will have:
Code:
09876sesese 2
12345wertwe 3
45678tgtgtg 1
_________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Joined: 17 Aug 2005 Posts: 3 Topics: 0 Location: Chennai
Posted: Wed Aug 17, 2005 7:44 am Post subject:
Hi Frank,
I have question out here:
If in Input File1: 12345xxxxx
& in Input File2: 12345xxxxx
That means the record not get updated but according to the solution it seems to me that it will give that record also where my reqmt is to get the updated records only in the output file.
It will be helpful if you can explain how this situation can be handeled. _________________ Thanks,
Hritam
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Wed Aug 17, 2005 10:59 am Post subject:
I gave a solution based on the original example given.
Are you saying you can have duplicates on positions 1-11 between the two files that you don't want in the output file, e.g.
Code:
File1
12345xdxdxd
09876sesese
22222xxxxxx
File2
12345wertwe
45678tgtgtg
22222xxxxxx
Output file
12345wertwe 3 - Updated record
09876sesese 2 - Deleted record
45678tgtgtg 1 - Inserted record
Or are you saying that you can have duplicate keys (positions 1-5) in file1, and duplicate keys in file2, e.g.
Code:
File1
12345xdxdxd
09876sesese
12345xxxxxx
File2
12345wertwe
45678tgtgtg
12345xxxxxx
Output file
?
I can't read your mind, so you need to tell me exactly what it is you're trying to do, with relevent examples that show all of the possibilities. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Last edited by Frank Yaeger on Thu Aug 18, 2005 10:23 am; edited 1 time in total
Here though both I/P Files have 12345abcdef but as its not updated it should not be populated in the O/P file where as 56789klmnop gets modified to 56789yyyyyy & that should be populated in the O/P file & the key here is (1-5).
Kindly let me know if i am clear this time. _________________ Thanks,
Hritam
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Thu Aug 18, 2005 10:50 am Post subject:
Hritam,
Here's a DFSORT/ICETOOL job that will do what you want:
Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1
//IN2 DD DSN=... input file2
//OUT DD DSN=... output file
//T1 DD DSN=&&T1,DISP=(MOD,PASS),UNIT=SYSDA,SPACE=(TRK,(5,5))
//T2 DD DSN=&&T1,DISP=(,PASS),UNIT=SYSDA,SPACE=(TRK,(5,5))
//TOOLIN DD *
* Add 11 identifier for FILE2 records.
COPY FROM(IN2) TO(T1) USING(CTL1) VSAMTYPE(F)
* Add 22 identifier for FILE1 records.
COPY FROM(IN1) TO(T1) USING(CTL2) VSAMTYPE(F)
* Remove fullly matching file1/file2 records
SELECT FROM(T1) TO(T2) ON(1,11,CH) NODUPS USING(CTL3)
* SPLICE to match up records and add id as follows:
* 1 = Inserted record
* 2 = Deleted record
* 3 = Updated record
SPLICE FROM(T2) TO(OUT) ON(1,5,CH) WITH(14,1) -
KEEPNODUPS USING(CTL4)
/*
//CTL1CNTL DD *
* Mark FILE1 records with '11'
OUTREC FIELDS=(1,11,13:C'11')
/*
//CTL2CNTL DD *
* Mark FILE2 records with '22'
OUTREC FIELDS=(1,11,13:C'22')
/*
//CTL3CNTL DD *
SORT FIELDS=(1,11,CH,A,13,2,CH,D)
//CTL4CNTL DD *
* Set id.
OUTFIL FNAMES=OUT,IFOUTLEN=13,
IFTHEN=(WHEN=(13,2,CH,EQ,C'21'),BUILD=(1,12,C'3')),
IFTHEN=(WHEN=NONE,BUILD=(1,13))
/*
OUT will have:
Code:
09876sesese 2
45678tgtgtg 1
56789klmnop 3
_________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
