SPLICE operator
.-----------. 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-'
Splices together specified fields from records with matching numeric or character field values (that is, duplicate values), but different information. This makes it possible to join fields from different types of input records to create an output record with information from two or more records.
Typically, you will want to reformat the records from two or more data sets to a temporary MOD data set, and use that temporary MOD data set as input to the SPLICE operator.
SPLICE examples shows some techniques for splicing records from different data sets together in a variety of ways to perform various file "join" and "match" operations.
By default (when WITHALL, WITHANY and WITHEACH are not specified), one spliced record is created for each set of duplicates by splicing the first duplicate with specified fields from the last duplicate.
The first duplicate is treated as a "base record". The last duplicate is treated as an "overlay record". Specified fields from the overlay record are overlaid on to the base record. Thus, the output record consists of fields from the base (first) record intermixed with specified fields from the overlay (last) record.
The records to be spliced can originate from two or more different input data sets.
From 1 to 10 ON fields can be used for the fields to match on. At least one ON(p,m,f) field must be specified; all such ON fields specified will be used to determine the matching records to be spliced together.
From 1 to 50 WITH fields can be used to specify the fields to be overlaid on the base record from the overlay record. At least one WITH(p,m) field must be specified; all such WITH fields specified will be overlaid on to the base record. All other fields in the base record will be kept unchanged.
BASE1 ON1 BASE2 BASE3 BASE4 GGGGG
ON1 WITHA WITHB
BASE1 ON1 BASE2 WITHA BASE3 BASE4 WITHB
25 | ON1 BASE1 BASE2
35 | ON1 WITH1 WITH2
the resulting spliced output
record would be: 25 | ON1 BASE1 WITH1 BASE2
The WITH2 field
is beyond the end of the base record, so it is not spliced.However, if you specify VLENMAX, the spliced record is given the larger of the base record length or overlay record length. If we specify VLENOVLY, the spliced record is given the overlay record length. In either case, if the overlay record length is larger, bytes in the extended spliced record that are not overlaid are filled in with blanks.
35 ON1 BASE1 WITH1 BASE2 WITH2
You can use
VLENMAX when you want the spliced record to have the maximum length
of the base or overlay record. You can use VLENOVLY when you want
the spliced record to have the length of the overlay record, regardless
of whether it's longer or shorter than the base record. Without VLENMAX
or VLENOVLY, the spliced record has the length of the base record
regardless of whether it's longer or shorter than the overlay record.
For fixed-length records, the length of the base, overlay and spliced records are all the same. Thus, VLENOVLY and VLENMAX have no meaning for fixed-length records and are ignored.
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.
The first duplicate is treated as a base record. Each subsequent duplicate is treated as an overlay record. The specified fields from each overlay record are overlaid on to the base record. Thus, the output records consist of fields from the base record intermixed with specified nonblank and blank fields from the overlay records.
The records to be spliced can originate from multiple input data sets.
BASE1 ON1 BASE2 BASE3 BASE4 GGGGG
ON1 WITHA WITHB
ON1 WITHC
ON1 WITHE WITHF
BASE1 ON1 BASE2 WITHA BASE3 BASE4 WITHB
BASE1 ON1 BASE2 WITHC BASE3 BASE4
BASE1 ON1 BASE2 WITHE BASE3 BASE4 WITHF
BASE1 ON1 BASE2 WITHE BASE3 BASE4 WITHF
30 | BASE1 ON1 BASE2
25 | ON1 WITHA
50 | ON1 WITHB WITHC WITHD
40 | ON1 WITHE WITHF
the resulting three spliced
output records would be: 30 | BASE1 ON1 WITHA BASE2
30 | BASE1 ON1 WITHB BASE2
30 | BASE1 ON1 WITHE BASE2
The WITHC, WITHD and WITHF fields are beyond the end of the base record, so they are not spliced.
30 | BASE1 ON1 WITHA BASE2
50 | BASE1 ON1 WITHB BASE2 WITHC WITHD
40 | BASE1 ON1 WITHE BASE2 WITHF
25 | BASE1 ON1 WITHA
50 | BASE1 ON1 WITHB BASE2 WITHC WITHD
40 | BASE1 ON1 WITHE BASE2 WITHF
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.
The first duplicate is treated as a base record. Each subsequent duplicate is treated as an overlay record. Each specified field with a nonblank value in each overlay record is overlaid on to the base record. Thus, the output record consists of fields from the base record intermixed with specified nonblank fields from each overlay record. The value from the last overlay record with each nonblank value will appear in the output record. Note that a specified "field" from an overlay record can actually consist of multiple fields from the record that have previously been reformatted into one contiguous field.
The records to be spliced can originate from multiple input data sets.
BASE1 ON1 BASE2
ON1 WITHC
ON1 WITHA
ON1 WITHB
BASE1 ON1 BASE2 WITHB WITHC WITHA
30 | BASE1 ON1 BASE2
50 | ON1 WITHB
25 | ON1 WITHA
40 | ON1 WITHC
30 | BASE1 ON1 WITHA BASE2
50 | BASE1 ON1 WITHA BASE2 WITHC WITHB
VLENOVLY cannot be specified with WITHANY.
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.
The first duplicate is treated as a base record. Each subsequent duplicate is treated as an overlay record. The specified blank or nonblank field from each overlay record is overlaid on to the base record. Thus, the output record consists of fields from the base record intermixed with a specified nonblank or blank field from each overlay record. Note that the specified "field" from an overlay record can actually consist of multiple fields from the record that have previously been reformatted into one contiguous field.
The records to be spliced can originate from multiple input data sets
BASE1 ON1 BASE2
ON1 WITHA
ON1 WITHB
ON1 WITHC
BASE1 ON1 BASE2 WITHA WITHB WITHC
30 | BASE1 ON1 BASE2
25 | ON1 WITHA
50 | ON1 WITHB
40 | ON1 WITHC
the resulting spliced
output records would be: 30 | BASE1 ON1 WITHA BASE2
The WITHB and WITHC fields are beyond the end of the base record, so they are not spliced.
50 | BASE1 ON1 WITHA BASE2 WITHC WITHB
VLENOVLY cannot be specified with WITHEACH.
KEEPNODUPS can be used to keep the non-duplicate records as well as the spliced records. The non-duplicate records will be unchanged.
UNIQA ONA
BASEA ONB
DUPAA ONB WITHA
UNIQB ONC
BASEB OND
DUPBB OND WITHB
The two unique records (ONA and OND) would
be kept along with the two spliced records (ONB and OND). The resulting
four unspliced and spliced output records would be: UNIQA ONA
BASEA ONB WITHA
UNIQB ONC
BASEB OND WITHB
BASEA ONB WITHA
BASEB OND WITHB
KEEPBASE can be used to keep the base records (first duplicate) as well as the spliced records. The base records will be unchanged.
UNIQA ONA
BASEA ONB
DUPAA ONB WITHA
UNIQB ONC
BASEB OND
DUPBB OND WITHB
The two base records with duplicates (first
ONB record and first OND record) would be kept along with the two
spliced records (ONB and OND). The resulting four unspliced and spliced
output records would be: BASEA ONB
BASEA ONB WITHA
BASEB OND
BASEB OND WITHB
Note that without KEEPBASE, the two base
records with duplicates (first ONB record and first OND record) would
not be kept. The resulting two spliced output records would be: BASEA ONB WITHA
BASEB OND WITHB
If we used KEEPNODUPS and KEEPBASE with the
original six records, the resulting six unspliced and spliced output
records would be: UNIQA ONA
BASEA ONB
BASEA ONB WITHA
UNIQB ONC
BASEB OND
BASEB OND WITHB
DFSORT is called to sort the indd data set. ICETOOL uses its E35 exit to determine which records to splice and include in the outdd data set. ICETOOL passes the EQUALS option to DFSORT to ensure that duplicates are kept in their original input order.
The DFSORT control statements in xxxxCNTL are used if USING(xxxx) is specified.
Do not supply your own MODS, SUM, OUTREC, or SORT statement.
- You can use an INCLUDE or OMIT statement to remove input records before SPLICE processing.
- You can use an INREC statement to reformat input records before SPLICE processing; the base and overlay records
are reformatted according to the INREC statement. You can use INREC's
PARSE, BUILD (FIELDS), OVERLAY, FINDREP, IFTHEN,
or IFOUTLEN functions. If your INREC statement changes the starting
position of an ON field or WITH field, you must specify the new starting
position for that ON field or WITH field. For example, if your input
records have a CH key at positions 1-5 and a WITH field at 6-8
and you use an INREC statement like this:
you must specify ON(31,5,CH) instead of ON(1,5,CH) and WITH(36,3) instead of WITH(6,3).INREC FIELDS=(31:1,50)
- You can further process the outdd records associated with TO(outdd) after SPLICE processing using an OUTFIL statement
like this:
or multiple OUTFIL statements like this:OUTFIL FNAMES=outdd,...
For example, with TO(OUT1) you could further modify the OUT1 records after they have been spliced, with a statement like this:OUTFIL FNAMES=outdd,... OUTFIL FNAMES=outdd1,... ...
OUTFIL FNAMES=OUT1,FTOV,VLTRIM=X'40'
- Override the DYNALLOC option using an OPTION control statement
such as:
in the xxxxCNTL data set.OPTION DYNALLOC=(,8)
- Use xxxxWKdd DD statements to override the use of dynamic allocation. Refer to SORTWKdd DD statement for details.
Tape work data sets cannot be used with ICETOOL.