Example 6 - Pull records from a master file in sorted order

This example shows how you can use the WITHALL operand to tell ICETOOL to use ON fields in a "PULL" data set to select one or more records from a "MASTER" data set. In other words, you can use a PULL list to select records from a MASTER list. In this case, the PULL data set has VB records and the MASTER data set has FB records. The ON field is a City Name that can be 1-20 bytes long, and the selected MASTER records are to be sorted by the City Name. (Example 7 - Pull records from a master file in their original order shows how to keep the MASTER records in their original order.)

//S6    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=&&T1,DISP=(MOD,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 'M' identifier to MASTER records.
 COPY FROM(MASTER) TO(TEMP1) USING(CTL2)
* Splice PULL and MASTER records (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(OUT) ON(1,20,CH) WITHALL WITH(1,40) -
   USING(CTL3)
/*
//CTL1CNTL DD *
* Convert PULL records from VB to FB and add 'P' identifier.
  OUTFIL FNAMES=TEMP1,VTOF,OUTREC=(5,20,41:C'P')
/*
//CTL2CNTL DD *
* Add 'M' identifier to MASTER records.
  OUTREC FIELDS=(1,40,41:C'M')
/*
//CTL3CNTL DD *
* Eliminate MASTER records without matching PULL records.
  OUTFIL FNAMES=OUT,OMIT=(41,1,CH,EQ,C'M'),OUTREC=(1,40)
/*

The base records originate from the PULL data set (VAR.PULL.FILE). The PULL data set has variable-length (VB) records with the RDW in positions 1-4 and the variable-length City Name starting in position 5 for 1-20 bytes. Conceptually, the PULL records look like this:

Length | Data
    12 | SAN JOSE
    12 | NEW YORK
    11 | DENVER
    15 | LOS ANGELES

The overlay records originate from the MASTER data set (FIXED.MASTER.FILE). The MASTER data set has 40-byte fixed-length (FB) records with the City Name in positions 1-20.

The PULL records are copied and reformatted to the TEMP1 data set as 41-byte fixed-length (FB) records with the City Name in positions 1-20 (padded on the right with blanks as necessary), and a 'P' in position 41 to identify them as PULL records. The VTOF and OUTREC parameters of DFSORT's OUTFIL statement are used to convert the VB records to FB records with blank padding. The reformatted PULL records in TEMP1 look like this:

SAN JOSE                                P
NEW YORK                                P
DENVER                                  P
LOS ANGELES                             P

The MASTER records are copied and reformatted to the end (MOD) of the TEMP1 data set as 41-byte fixed-length (FB) records with an 'M' added in position 41 to identify them as MASTER records. The reformatted MASTER records in TEMP1 look like this:

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

The base and overlay records from the TEMP1 data set are sorted and spliced.

The records look like this after they are sorted on the 1,20,CH field, but before they are spliced. As a visual aid, the WITH fields in the overlay records are shown in bold.

DENVER                                  P
LOS ANGELES                             P
LOS ANGELES          9203  MICHAEL      M
NEW YORK                                P
NEW YORK             5218  CARRIE       M
NEW YORK             6385  MICHAEL      M
PHOENIX              7993  PAUL         M
PHOENIX              5831  HOLLY        M
SAN JOSE                                P
SAN JOSE             8630  SUSAN        M
SAN JOSE             0052  VICKY        M
SAN JOSE             3896  FRANK        M
TUCSON               1056  LISA         M

The spliced records look like this:

LOS ANGELES          9203  MICHAEL      P
NEW YORK             5218  CARRIE       P
NEW YORK             6385  MICHAEL      P
PHOENIX              5831  HOLLY        M
SAN JOSE             8630  SUSAN        P
SAN JOSE             0052  VICKY        P
SAN JOSE             3896  FRANK        P

Finally, we use the OUTFIL statement for SPLICE to remove each spliced record with an 'M' in position 41, because that represents a base record without a matching overlay record. The OUTFIL statement also removes the 'P' indicator in position 41 from each record, because it is not needed in the OUT data set.

Thus, for each MASTER record that matches a PULL record, we've overlaid the PULL record with the MASTER record. This effectively selects all of the MASTER records on the PULL list. The resulting OUT data set (FIXED.OUTPUT.FILE) has the following 40-byte fixed-length records:

LOS ANGELES          9203  MICHAEL
NEW YORK             5218  CARRIE
NEW YORK             6385  MICHAEL
SAN JOSE             8630  SUSAN
SAN JOSE             0052  VICKY
SAN JOSE             3896  FRANK