Posted: Tue Mar 01, 2005 3:35 pm Post subject: Merging of the files and summing
Hi,
I have slight change from what I saw for merging the files. Chances are that I could have missed this in the prior posts also. If so let me know the link for it.
Here is what I need
File 1: - (Not in any order and can have duplicates). FB / 15 bytes.
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Tue Mar 01, 2005 4:37 pm Post subject:
If you have z/OS DFSORT V1R5 PTF UQ95214 or DFSORT R14 PTF UQ95213 (Dec, 2004), you can use the following DFSORT/ICETOOL job to do what you want:
Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/15)
//IN2 DD DSN=... input file2 (FB/15)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),
//**** Use MOD for T1
// DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=... output file
//TOOLIN DD *
SORT FROM(IN1) TO(T1) USING(CTL1)
COPY FROM(IN2) TO(T1) USING(CTL2)
SORT FROM(T1) TO(T2) USING(CTL3)
SORT FROM(T2) TO(OUT) USING(CTL4)
//CTL1CNTL DD *
* IN1->T1:
* Add seqnum so we can get records back in their original order.
INREC OVERLAY=(16:SEQNUM,8,ZD)
* Sort on key.
SORT FIELDS=(1,4,CH,A)
* Add restart number. Restart when key changes.
* First record with each key have 1 for restart number,
* second record with each key will have 2, etc.
OUTREC OVERLAY=(24:SEQNUM,8,ZD,RESTART=(1,4))
/*
//CTL2CNTL DD *
* IN2-T1:
* Set restart number to 1 so only the first record
* with each key from file1 will be summed with the
* first record with each key from file2.
INREC OVERLAY=(24:C'00000001')
/*
//CTL3CNTL DD *
* T1->T2:
OPTION EQUALS,ZDPRINT
* Sort on key and restart number. Will only sum records
* with restart number of 1.
SORT FIELDS=(1,4,CH,A,24,8,ZD,A)
* Sum on ZD field.
SUM FIELDS=(6,2,ZD)
* Remove restart number.
OUTREC FIELDS=(1,23)
/*
//CTL4CNTL DD *
* Sort on seqnum to get records back in their original order.
SORT FIELDS=(16,8,ZD,A)
* Remove seqnum.
OUTREC FIELDS=(1,15)
/*
If you have DFSORT, but you don't have the Dec, 2004 PTF, ask your System Programmer to install it (it's free). _________________ 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: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Tue Mar 01, 2005 5:17 pm Post subject:
Quote:
can I have the solution using SYNCSORT
Pavani,
Not from me. I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort. Somebody else will have to help you with that. _________________ 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: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Wed Mar 02, 2005 1:07 pm Post subject:
Pavani,
The following Syncsort JCL will give you the desired results.
A brief explanation of the job. The first step adds a seqnum to all the records and sorts on the first 4 bytes of the IN1 file and eliminate dups. We also use OPTION EQUALS to pick the first record of each key.
SUM FIELDS=NONE,XSUM eliminates the dups and writes them to a XSUM dd file. Here in this case the duplicate records are written to CTL1XSUM file. The unique records are written to T1 file.
The Second COPY operator takes the IN2 file and merely copies adding 8 spaces at the end and creates a File T2
Now we concatenate t1 & t2 and sort on the key and sum on the qty at pos 6. By doing so the values of file2 are added to the first record of file 1. This is written to file T3
Now once again we concatenate the duplicat file(ctl1xsum) and t3 together and sort on the seqnum to retain the orginal order of the records. While writting out we remove the seqnum.
Code:
//STEP0100 EXEC PGM=SYNCTOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
0001 05
0005 03
0016 14
0002 02
0005 03
0003 05
0016 14
//IN2 DD *
0001 01
0002 01
0003 01
0005 01
0016 01
//T1 DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//CTL1XSUM DD DSN=&C1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//T2 DD DSN=&T2,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//T3 DD DSN=&T3,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//CON1 DD DSN=&T1,DISP=OLD,VOL=REF=*.T1
// DD DSN=&T2,DISP=OLD,VOL=REF=*.T2
//CON2 DD DSN=&T3,DISP=OLD,VOL=REF=*.T3
// DD DSN=&C1,DISP=OLD,VOL=REF=*.CTL1XSUM
//OUT DD SYSOUT=*
//TOOLIN DD *
SORT FROM(IN1) USING(CTL1)
COPY FROM(IN2) USING(CTL2)
SORT FROM(CON1) USING(CTL3)
SORT FROM(CON2) USING(CTL4)
//CTL1CNTL DD *
OPTION EQUALS $ ENSURES ORDER OF RECS
INREC FIELDS=(1,15, $ TOT LRECL
SEQNUM,8,ZD) $ SEQNUM
SORT FIELDS=(1,4,CH,A) $ SORT ON KEY
SUM FIELDS=NONE, $ ELIMINATE DUPS
XSUM $ WRITE DUP RECS TO XSUM
OUTFIL FNAMES=T1 $ WRITE UNIQUE RECS TO T1
//CTL2CNTL DD *
OUTFIL FNAMES=T2, $ WRITE RECS TO T2
OUTREC=(1,15,8X) $ TOTAL LRECL + 8 SPACES
//CTL3CNTL DD *
OPTION EQUALS,ZDPRINT $ ENSURE ORDER OF RECS
SORT FIELDS=(1,4,CH,A) $ SORT ON THE KEY
SUM FIELDS=(6,2,ZD) $ SUM ON THE QTY
OUTFIL FNAMES=T3, $ OUTPUT TO T3 FILE
OMIT=(16,8,CH,EQ,C' ') $ OMIT NON-MATCH RECS FROM IN2
//CTL4CNTL DD *
SORT FIELDS=(16,8,CH,A) $ SORT ON SEQNUM
OUTFIL FNAMES=OUT, $ OUTPUT FILE
OUTREC=(1,15) $ REMOVE SEQNUM
/*
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
If so let me know the link for it.
