SPLICE normally requires reformatting the records of two or more data sets so they can be JOINED. In other words, SPLICE specified fields together from records that have the same specified numeric or character field values (duplicate values) but with different information. Because SPLICE overlays the WITH fields from the overlay record to the base record using matching ON fields, it’s usually necessary to do some initial setup before using SPLICE, to ensure that:
- the ON fields are in the same positions in the base and overlay records
- the WITH fields in the overlay records are in the positions they will occupy in the base records
- the base records and overlay records are the same length. This is always required for fixed-length records and is required for variable-length records unless VLENMAX or VLENOVLY is specified.
.-----------. V | >>-SPLICE--FROM(indd)--TO(outdd)----ON(p,m,f)-+-----------------> .-----------. V | >----WITH(p,m)-+--+----------+--+------------+--+----------+----> +-WITHALL--+ '-KEEPNODUPS-' '-KEEPBASE-' +-WITHANY--+ '-WITHEACH-' >--+-------------+--+-------------+--+-------+--+----------+--->< '-USING(xxxx)-' '-VSAMTYPE(x)-' '-UZERO-' +-VLENMAX--+ '-VLENOVLY-'
SPLICE Required Operands
FROM –Specifies DDNAME of the input data set.
TO – Specifies the DDNAME of the output data set for the selected records.
ON – Specifies a field to be used for this operation. From 1 to 10 ON fields can be specified.
– (p,m,f) gives the position, length, and format of a numeric or character field. A field must not extend beyond position 32752 or the end of the record.
WITH(p,m) – Specifies a field to be spliced from the second or subsequent duplicate record to the first duplicate record. From 1 to 50 WITH fields can be specified. (p,m) gives the position and length of the field.
SPLICE Optional Operands
WITHALL – Can be used to create multiple spliced records for each set of duplicates. The first duplicate is spliced with the specified fields from the second duplicate. Then the first duplicate is spliced with the specified fields from the third duplicate, and so on.
WITHANY – Can be used to create one spliced record for each set of duplicates. The first duplicate is spliced with the nonblank values of each subsequent duplicate for specified fields.
WITHEACH – Can be used to create one spliced record for each set of duplicates. The first duplicate is spliced with one specified field from each subsequent duplicate.
KEEPNODUPS – Can be used to keep the non-duplicate records as well as the spliced records. The non-duplicate records will be unchanged.
KEEPBASE – Can be used to keep the base records (first duplicate) as well as the spliced records. The base records will be unchanged.
VSAMTYPE – Specifies the record format for a VSAM input data set (F or V).
UZERO – Causes -0 to be treated as unsigned, that is, as +0.
USING – Specifies the first 4 characters of the DDNAME (xxxxCNTL) for the DFSORT control statement data set.
Example: Create one spliced record for each match in two files: Splice data together for each pair of records with the same ON field in two different input data sets.
//S1 EXEC PGM=ICETOOL //TOOLMSG DD SYSOUT=* //DFSMSG DD SYSOUT=* //IN1 DD * SL1 POSN1 T01 SL1 POSN4 T01 SL2 POSN5 T02 SL1 POSN6 T01 SL2 POSN3 T02 /* //IN2 DD * POSN1 27M $100 SAN JOSE CA POSN2 08M $200 BOSTON MA POSN3 10M $300 BUFFALO NY POSN4 50M $50 NEWARK NJ /* //TEMP1 DD DSN=&&TEMP1, // DISP=(MOD,PASS), // SPACE=(TRK,(5,5)),UNIT=SYSDA //COMBINE DD SYSOUT=* //TOOLIN DD * * Reformat the File1 records for splicing COPY FROM(IN1) TO(TEMP1) USING(CTL1) * Reformat the File2 records for splicing COPY FROM(IN2) TO(TEMP1) USING(CTL2) * Splice the needed data from File1 and File2 together SPLICE FROM(TEMP1) TO(COMBINE) ON(5,5,CH) WITH(15,11) /* //CTL1CNTL DD * OUTREC FIELDS=(1,14, file1 data 25:X) add blanks for spliced file2 data /* //CTL2CNTL DD * OUTREC FIELDS=(5:1,5, put file2 key in same place as file1 key 15:7,8, file2 data 30:33,2) file2 data /*
Base records originate from the IN1 data set. The reformatted TEMP1 records are 31 bytes long and look like this:
SL1 POSN1 T01 SL1 POSN4 T01 SL2 POSN5 T02 SL1 POSN6 T01 SL2 POSN3 T02
The overlay records originate from the IN2 data set. The reformatted TEMP1 records are 31 bytes long and look like this:
POSN1 27M $100 CA POSN2 08M $200 MA POSN3 10M $300 NY POSN4 50M $50 NJ
The records look like this after they are sorted on the 5,5, CH field, but before they are spliced. As a visual aid, the WITH fields in the overlay records are shown in bold.
SL2 POSN5 T02 SL2 POSN3 T02 POSN3 10M $300 NY SL1 POSN4 T01 POSN4 50M $50 NJ POSN2 08M $200 MA SL1 POSN1 T01 POSN1 27M $100 CA SL1 POSN6 T01
The spliced COMBINE records are 31 bytes long and look like this:
SL2 POSN3 T02 10M $300 NY SL1 POSN4 T01 50M $50 NJ SL1 POSN1 T01 27M $100 CA
Example: The requirement is to have an output record for each SLx value with the Nx value for Pos1, Pos2, and Pos3. If Posx is missing for a particular SLx value, the Nx value should be left blank.
So the output records should come as mentioned below:
SL1 N5 N2 N8 Comment1 SL2 N3 XX N9 Comment2 SL3 N0 N7 XX Comment3 SL4 N6 XX XX Comment4 SL5 XX N3 N8 Comment5 SL6 XX N4 XX Comment6 *In the output X represents one SPACE
This requirement can be fulfilled by the use of SPLICE with WITHANY.
//S1 EXEC PGM=ICETOOL //TOOLMSG DD SYSOUT=* //DFSMSG DD SYSOUT=* //IN DD DSN=XXX.YYYY.ZZZZZ.INPUT <—- input file //OUT DD DSN=XXX.YYYY.ZZZZZ.OUTPUT <—- output file //TOOLIN DD * * Splice records with matching SLx values into one record * with nonblank Nx values for Pos1, Pos2 and Pos3. SPLICE FROM(IN) TO(OUT) ON(1,3,CH) WITHANY KEEPNODUPS - WITH(5,2) WITH(8,2) WITH(11,2) USING(CTL1) /* //CTL1CNTL DD * If record has 'Pos1', reformat it to: SLx Nx Commentx INREC IFTHEN=(WHEN=(8,1,ZD,EQ,1), BUILD=(1,3,5:10,2,14:14,8)), If record has 'Pos2', reformat it to: SLx Nx Commentx IFTHEN=(WHEN=(8,1,ZD,EQ,2), BUILD=(1,3,8:10,2,14:14,8)), If record has 'Pos3', reformat it to: SLx Nx Commentx IFTHEN=(WHEN=(8,1,ZD,EQ,3), BUILD=(1,3,11:10,2,14:14,8)) /*
INREC IFTHEN processing is used to reformat the Pos1, Pos2, and Pos3 records so the corresponding Nx value is in the correct place for splicing. After INREC processing, the intermediate records look like this:
Intermediate Result: SL1 N5 XX XX Comment1 SL1 XX N2 XX Comment1 SL1 XX XX N8 Comment1 SL2 N3 XX XX Comment2 SL2 XX XX N9 Comment2 SL3 N0 XX XX Comment3 SL3 XX N7 XX Comment3 SL4 XX XX N6 Comment4 SL5 XX N3 XX Comment5 SL5 XX XX N8 Comment5 SL6 XX N4 XX Comment6 ** XX - SPACE
Note that the Nx value for a Pos1 record is in positions 5-6, the Nx value for a Pos2 record is in positions 8-9, and the Nx value for a Pos3 value is in positions 11-12. SPLICE splices the records with the same SLx value into one record. WITHANY tells SPLICE to keep any nonblank WITH field value from records with the same key. WITH(5,2), WITH(8,2), and WITH(11,2) specify the WITH fields. For example, for the SL2 records, the nonblank value (N3) in positions 5-6 of the first record, and the nonblank value (N9) in positions 11-12 of the second record is kept. Since there’s no nonblank value in positions 8-9, that WITH field is left blank. So nonblank fields are kept and missing fields are ignored. The resulting output is:
Final Output: SL1 N5 N2 N8 Comment1 SL2 N3 XX N9 Comment2 SL3 N0 N7 XX Comment3 SL4 XX XX N6 Comment4 SL5 XX N3 N8 Comment5 SL6 XX N4 XX Comment6
Example: Pull records from a master file in their original order as present in the original file.
We use the SEQNUM parameter to add a sequence number to each MASTER record before the records are spliced, and we splice that sequence number along with the data. After SPLICE sorts by the City Name, we SORT again by the sequence number to get the resulting MASTER records back in their original order.
Input - TEMP1 SAN JOSE P NEW YORK P DENVER P LOS ANGELES P Input - TEMP2 SAN JOSE 8630 SUSAN M PHOENIX 7993 PAUL M LOS ANGELES 9203 MICHAEL M SAN JOSE 0052 VICKY M NEW YORK 5218 CARRIE M SAN JOSE 3896 FRANK M TUCSON 1056 LISA M NEW YORK 6385 MICHAEL M PHOENIX 5831 HOLLY M
//S7 EXEC PGM=ICETOOL //TOOLMSG DD SYSOUT=* //DFSMSG DD SYSOUT=* //PULL DD DSN=VAR.PULL.FILE,DISP=SHR //MASTER DD DSN=FIXED.MASTER.FILE,DISP=SHR //TEMP1 DD DSN=&&TEMP1,DISP=(MOD,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA //TEMP2 DD DSN=&&TEMP2,DISP=(,PASS),SPACE=(TRK,(5,5)),UNIT=SYSDA //OUT DD DSN=FIXED.OUTPUT.FILE,DISP=(NEW,CATLG,DELETE), // SPACE=(TRK,(5,5)),UNIT=SYSDA //TOOLIN DD * * Convert PULL records from VB to FB and add ’P’ identifier. COPY FROM(PULL) USING(CTL1) * Add sequence number and ’M’ identifier to MASTER records. COPY FROM(MASTER) TO(TEMP1) USING(CTL2) * Splice PULL and MASTER records (splice sequence number, but * do NOT splice identifier): * Spliced MASTER records with matching PULL records have ’P’ id. * Spliced MASTER records without matching PULL records have ’M’ id. * Eliminate records with ’M’ id. SPLICE FROM(TEMP1) TO(TEMP2) ON(1,20,CH) WITHALL WITH(1,48) - USING(CTL3) * Sort resulting spliced records on original sequence number * to get them back in their original order. Remove id and sequence number. SORT FROM(TEMP2) TO(OUT) USING(CTL4) /* //CTL1CNTL DD * * Convert PULL records from VB to FB and add ’P’ identifier OUTFIL FNAMES=TEMP1,VTOF,OUTREC=(5,20,49:C’P’) /* //CTL2CNTL DD * * Add sequence number and ’M’ identifier to MASTER records. OUTREC FIELDS=(1,40,41:SEQNUM,8,BI,49:C’M’) /* //CTL3CNTL DD * * Eliminate MASTER records without matching PULL records. OUTFIL FNAMES=TEMP2,OMIT=(49,1,CH,EQ,C’M’) /* //CTL4CNTL DD * * Sort on sequence number and remove id and sequence number. SORT FIELDS=(41,8,BI,A) OUTREC FIELDS=(1,40) /*
The resulting OUT data set (FIXED.OUTPUT.FILE) has the following 40-byte fixed-length records:
SAN JOSE 8630 SUSAN LOS ANGELES 9203 MICHAEL SAN JOSE 0052 VICKY NEW YORK 5218 CARRIE SAN JOSE 3896 FRANK NEW YORK 6385 MICHAEL