SPLICE Operator

Joins the records using common fields (SPLICE Operator) Example

SPLICE Operator Tutorial   

Scenario -Create one spliced record for each match in two files.

In this example, we are joining employee details with employee salary based on employee number(columns 1-3).

INPUT1 - MATEPK.INPUT.PSFILE

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
001  PAWAN         MAINFRAME           JPM       AP        IN
002  SRINIVAS      TESTING             ORACLE    TG        IN
003  SRIDHAR       SAS                 CG        OR        US
004  VENKATESH     ABAP                CSC       CA        IN
005  RAVI          HADOOP              CTS       FL        US
006  PRASAD        HR                  INFOSYS   MI        US
007  RAJA          TESTING             IBM       CA        US

INPUT2 - MATEPK.INPUT.PSFILE3

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
001      0000100000
002      0000095000
003      0000080000
004      0000053000
005      0000072000
006      0000066000
007      0000047000

JCL -

----+----1----+----2----+----3----+----4----+----5----+
//MATEPKSP JOB (123),'MATEPK',CLASS=A,MSGCLASS=A,
//             MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*
//STEP01   EXEC PGM=ICETOOL
//INDD1    DD DSN=MATEPK.INPUT.PSFILE,DISP=SHR 
//INDD2    DD DSN=MATEPK.INPUT.PSFILE3,DISP=SHR 
//TEMP     DD DSN=&&TEMP,DISP=(MOD,PASS),
//            SPACE=(TRK,(5,5)),UNIT=SYSDA,
//            DCB=(DSORG=PS,RECFM=FB,LRECL=80,BLKSIZE=800)
//OUTDD    DD DSN=MATEPK.OUTPUT.PSFILESP,
//            DISP=(NEW,CATLG,DELETE),VOLUME=SER=DEVHD4,
//            SPACE=(TRK,(1,1),RLSE),UNIT=SYSDA,
//            DCB=(DSORG=PS,RECFM=FB,LRECL=80,BLKSIZE=800)
//TOOLIN   DD *
  COPY FROM(INDD1) TO(TEMP) USING(CTL1)
  COPY FROM(INDD2) TO(TEMP) USING(CTL2)
  SPLICE FROM(TEMP) TO(OUTDD) ON(1,3,ZD) WITH(70,10)
/*
//CTL1CNTL DD *
  OUTREC FIELDS=(1,69,70:X)
/*
//CTL2CNTL DD *
  OUTREC FIELDS=(1:1,3,70:10,10)
/*
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//

OUTPUT - MATEPK.OUTPUT.PSFILE

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
001  PAWAN         MAINFRAME           JPM       AP        IN        0000100000 
002  SRINIVAS      TESTING             ORACLE    TG        IN        0000095000 
003  SRIDHAR       SAS                 CG        OR        US        0000080000 
004  VENKATESH     ABAP                CSC       CA        IN        0000053000 
005  RAVI          HADOOP              CTS       FL        US        0000072000 
006  PRASAD        HR                  INFOSYS   MI        US        0000066000 
007  RAJA          TESTING             IBM       CA        US        0000047000

TOOLMSG (SDSF SPOOL) - Verify TOOLMSG for the return code of the submitted job.

SPLICE TOOLMSG

Explaining Example -

  • INDD - Specifies the ddname for input file.
  • OUTDD - Specifies the ddname for output file.
  • TOOLIN DD * - Specifies the ICETOOL statements for DFSORT.
  • TOOLMSG - Specifies where to write the ICETOOL processing messages.
  • DFSMSG - Specifies where to write the DFSORT processing messages.
  • COPY FROM(INDD1) TO(TEMP) USING(CTL1) - Writes the records to the temp file using CTL1.
  • OUTREC FIELDS=(1,69,70:X) - Format the record filling spaces from 70th column.
  • COPY FROM(INDD2) TO(TEMP) USING(CTL2) - Writes the records to the temp file using CTL2.
  • OUTREC FIELDS=(1:1,3,70:10,10) - Reformat the record moving data from 10-19 columns to 70-79 columns.
  • SPLICE FROM(TEMP) TO(OUTDD) ON(1,3,ZD) WITH(70,10) - Joins the records from INDD1, INDD2 together based on the key value (1-3 columns).