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.

To illustrate the splicing process, if we had the following two fixed-length input records with the base fields, ON field and WITH fields as shown:

BASE1  ON1    BASE2           BASE3 BASE4  GGGGG
       ON1            WITHA                WITHB

the resulting spliced output record would be:

BASE1  ON1    BASE2   WITHA   BASE3 BASE4  WITHB

For variable-length records, by default (without VLENMAX or VLENOVLY), the spliced record has the same length as the base record, and WITH fields from the overlay record that are beyond the end of the base record do not appear in the spliced record. For example, if we had the following two records with the lengths (in the RDW), ON field and WITH fields as shown:

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.

The resulting spliced output record with either VLENMAX or VLENOVLY would be:

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.

To illustrate the splicing process when WITHALL is specified, if we had the following four fixed-length records with the base fields, ON field and WITH fields as shown:

BASE1  ON1    BASE2           BASE3 BASE4  GGGGG
       ON1            WITHA                WITHB
       ON1            WITHC
       ON1            WITHE                WITHF

The resulting three spliced output records would be:

BASE1  ON1    BASE2   WITHA   BASE3 BASE4  WITHB
BASE1  ON1    BASE2   WITHC   BASE3 BASE4
BASE1  ON1    BASE2   WITHE   BASE3 BASE4  WITHF

Note that without WITHALL, the resulting single spliced output record would be:

BASE1  ON1    BASE2   WITHE   BASE3 BASE4  WITHF

For variable-length records, by default (without VLENMAX or VLENOVLY), the spliced record has the same length as the base record, and WITH fields from the overlay record that are beyond the end of the base record do not appear in the spliced record. For example, with WITHALL, if we had the following four records with the lengths (in the RDW), ON field and WITH fields as shown:

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

The resulting three spliced output records with WITHALL and VLENOVLY would be:

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.

To illustrate the splicing process when WITHANY is specified, if we had the following four fixed-length records with the base fields, ON field and WITH fields as shown:

BASE1  ON1     BASE2
       ON1                    WITHC
       ON1                           WITHA
       ON1            WITHB

The resulting spliced output record would be:

BASE1  ON1     BASE2  WITHB   WITHC  WITHA

For variable-length records, by default (without VLENMAX), the spliced record has the same length as the base record. For example, with WITHANY, if we had the following four records with the lengths (in the RDW), ON field and WITH fields as shown:

30 | BASE1 ON1           BASE2
50 |       ON1                               WITHB
25 |       ON1     WITHA
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. However, if you specify VLENMAX, the spliced record is given the largest of the base record length or overlay record lengths. If the largest overlay record length is larger than the base record length, bytes in the extended spliced record that are not overlaid are filled in with blanks. The resulting spliced output record with WITHANY and VLENMAX would be:

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

To illustrate the splicing process when WITHEACH is specified, if we had the following four records with the base fields, ON field and WITH fields as shown:

BASE1  ON1     BASE2
       ON1            WITHA
       ON1                    WITHB
       ON1                           WITHC

The resulting spliced output record would be:

BASE1  ON1     BASE2  WITHA   WITHB  WITHC

For variable-length records, by default (without VLENMAX), the spliced record has the same length as the base record. For example, with WITHEACH, if we had the following four records with the lengths (in the RDW), ON field and WITH fields as shown:

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.

However, if you specify VLENMAX, the spliced record is given the largest of the base record length or overlay record lengths. If the largest overlay record length is larger than the base record length, bytes in the extended spliced record that are not overlaid are filled in with blanks. The resulting spliced output record with WITHEACH and VLENMAX would be:

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.

To illustrate the splicing process when KEEPNODUPS is specified, if we had the following six records with the base fields, ON fields and WITH fields as shown:

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

Note that without KEEPNODUPS, the two unique records (ONA and ONC) would not be kept. The resulting two spliced output records would be:

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.

To illustrate the splicing process when KEEPBASE is specified, if we had the following six records with the base fields, ON fields and WITH fields as shown:

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 comment statements. You can use INCLUDE, OMIT, INREC, OPTION, and OUTFIL statements providing you observe these rules:

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:
```
  INREC FIELDS=(31:1,50)
```
you must specify ON(31,5,CH) instead of ON(1,5,CH) and WITH(36,3) instead of WITH(6,3).
You can further process the outdd records associated with TO(outdd) after SPLICE processing using an OUTFIL statement like this:
```
  OUTFIL FNAMES=outdd,...
```
or multiple OUTFIL statements like this:
```
  OUTFIL FNAMES=outdd,...
  OUTFIL FNAMES=outdd1,...
  ...  
```
For example, with TO(OUT1) you could further modify the OUT1 records after they have been spliced, with a statement like this:
```
  OUTFIL FNAMES=OUT1,FTOV,VLTRIM=X'40'
```

The DYNALLOC option is passed to DFSORT to ensure that work space is available for the sort. If your installation defaults for dynamic allocation are inappropriate for a SPLICE operator, you can specify USING(xxxx) and take one of the following actions:

Override the DYNALLOC option using an OPTION control statement such as:
```
  OPTION DYNALLOC=(,8)
```
in the xxxxCNTL data set.
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.