Example 1 - Create one spliced record for each match in two files
This example shows how you can 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 *
Y12 89503 MKT
Y12 57301 MKT
Z35 02316 DEV
Y12 91073 MKT
Z35 18693 DEV
/*
//IN2 DD *
89503 27M $9,185,354 SAN JOSE CA
72135 08M $317,632 BOSTON MA
18693 10M $8,732,105 BUFFALO NY
57301 50M $30,000 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,ZD) WITH(15,17)
/*
//CTL1CNTL DD *
OUTREC FIELDS=(1,14, file1 data
31: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,15, file2 data
30:33,2) file2 data
/*
The base records originate from the IN1 data set and are copied
and reformatted to the TEMP1 data set. The reformatted TEMP1 records
are 31 bytes long and look like this:
Y12 89503 MKT
Y12 57301 MKT
Z35 02316 DEV
Y12 91073 MKT
Z35 18693 DEV
The overlay records originate from the IN2 data
set and are copied and reformatted to the end (MOD) of the TEMP1
data set. The reformatted TEMP1 records are 31 bytes long and look
like this:
89503 27M $9,185,354 CA
72135 08M $317,632 MA
18693 10M $8,732,105 NY
57301 50M $30,000 NJ
Note that MOD
is used for the TEMP1 data set, so the reformatted records from IN1
and IN2 will be output to the TEMP1 data set in that order, ensuring
that they are spliced in that order.The base and overlay records from the TEMP1 data set are sorted and spliced to the COMBINE data set.
The records look like this after they are
sorted on the 5,5,ZD field, but before they
are spliced. As a visual aid, the WITH fields in the overlay records
are shown in bold.
Z35 02316 DEV
Z35 18693 DEV
18693 10M $8,732,105 NY
Y12 57301 MKT
57301 50M $30,000 NJ
72135 08M $317,632 MA
Y12 89503 MKT
89503 27M $9,185,354 CA
Y12 91073 MKT
The spliced COMBINE records are 31 bytes long and look like this:
Z35 18693 DEV 10M $8,732,105 NY
Y12 57301 MKT 50M $30,000 NJ
Y12 89503 MKT 27M $9,185,354 CA
Note that the base records for 18693, 57301 and 89503 have been spliced together with their respective overlay records.
Here is what the various ICETOOL operators do in this job:
- The first COPY operator creates reformatted IN1 records in TEMP1. The second COPY operator creates reformatted IN2 records in TEMP1. The reformatted IN1 records have blanks where the reformatted IN2 WITH fields will go. The reformatted IN2 records have the ON field from IN2 in the same place as in the reformatted IN1 records, and have the IN2 data where we want it to go in the reformatted IN1 records. We made the reformatted IN1 and reformatted IN2 records the same size so we can put them all in the TEMP1 data set and use TEMP1 as input to the SPLICE operator.
- The SPLICE operator sorts the records from TEMP1 using the ON field. TEMP1 has the reformatted IN1 records before the reformatted IN2 records. The spliced records are created from the base records and the overlay records in TEMP1. Whenever two records are found with the same ON field, the WITH field from the second record (reformatted IN2 overlay record) is overlaid on to the first record (reformatted IN1 base record). The resulting spliced records are written to the COMBINE data set.