Load JCL with a cursor to copy data from a remote DB2 subsystem

This is an example of a job that uses a cursor in a load utility to copy data from a remote DB2 subsystem to a local DB2 subsystem. The assumption here is that there is connectivity between the subsystem and the table structures are identical. If they are not identical than changes need to be made in the cursor declaration section to reflect this.

I find this very useful and I need to quickly copy some data for a user. You can also include predicate conditions in the cursor declaration to limit the amount of data that you are transferring. This particular example is created to replace the data in the target table and that is why I’m taking an image copy before I do the load in case a recovery is needed.

//
//<Put your job header information here>
//*
//*--------------------------------------------------------------------
//*
//* IMAGE COPY AND SET UP A RECOVERY POINT AT THE TARGET (LOCAL = DEA) 
//* SITE
//*
//*--------------------------------------------------------------------
//IMAGECPA EXEC DSNUPROC,SYSTEM=DEVA,UTPROC=''
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
  LISTDEF MYTABS
          INCLUDE TABLESPACE <database name>.<table space name>
 
  TEMPLATE
          COPYDS DSN      (<HLQ>.&TS..IC.D&DATE..T&TIME.)
                 DISP     (NEW,CATLG,CATLG)
                 STORCLAS  STANDARD
                 MGMTCLAS  PERM
                 DATACLAS  PSFB
                 RETPD     366
 
  COPY LIST      MYTABS
       FULL      YES
       SHRLEVEL  REFERENCE
       COPYDDN  (COPYDS)
 
  QUIESCE LIST   MYTABS
          WRITE  YES
/*
//*--------------------------------------------------------------------
//*
//* LOAD THE DATA
//*
//*--------------------------------------------------------------------
//LOAD     EXEC DSNUPROC,SYSTEM=DEVA,UTPROC='',COND=(0,LT)
//STEPLIB  DD DSN=DSNDEVA.SDSNLOAD,DISP=SHR
//            INCLUDE MEMBER=DB2DEVA
//*//SORTWK01 DD UNIT=SYSDA,
//*//            SPACE=(CYL,(5000,2000),RLSE),
//*//            DCB=BUFNO=64
//*//SORTWK02 DD UNIT=SYSDA,
//*//            SPACE=(CYL,(5000,2000),RLSE),
//*//            DCB=BUFNO=64
//*//SORTWK03 DD UNIT=SYSDA,
//*//            SPACE=(CYL,(5000,2000),RLSE),
//*//            DCB=BUFNO=64
//*//SORTWK04 DD UNIT=SYSDA,
//*//            SPACE=(CYL,(5000,2000),RLSE),
//*//            DCB=BUFNO=64
//SORTOUT  DD UNIT=SYSDA,
//            SPACE=(CYL,(50,100),RLSE),
//            VOL=(,,,99),
//            DCB=BUFNO=64
//SYSUT1   DD UNIT=SYSDA,
//            SPACE=(CYL,(50,100),RLSE),
//            VOL=(,,,99),
//            DCB=BUFNO=64
//SYSDISC  DD DSN=<HLQ>.<database name>.<table space name>.DSC,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,
//            SPACE=(CYL,(50,100),RLSE),
//            VOL=(,,,99),
//            DCB=BUFNO=64
//SYSMAP   DD DSN=<HLQ>.<database name>.<table space name>.MAP,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,
//            SPACE=(CYL,(50,100),RLSE),
//            VOL=(,,,99),
//            DCB=BUFNO=64
//SYSOUT   DD DSN=<HLQ>.<database name>.<table space name>.OUT,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,
//            SPACE=(CYL,(50,10),RLSE),
//            DCB=BUFNO=64
//SYSPRINT DD SYSOUT=*
//*SYSPRINT DD DSN=<HLQ>.<database name>.<table space name>.LDPRN,
//*            DISP=(NEW,CATLG,CATLG),
//*            UNIT=SYSDA,
//*            SPACE=(CYL,(500,500),RLSE),
//*            DCB=BUFNO=64
//SYSERR   DD DSN=<HLQ>.<database name>.<table space name>.ERR,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,
//            SPACE=(CYL,(500,100),RLSE),
//            DCB=BUFNO=64
//DSSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSIN DD *
 
  EXEC SQL
       DECLARE C01 CURSOR FOR
       SELECT  *
       FROM    <remote site>.<source table owner>.<source table name>
       WITH    UR
  ENDEXEC
 
  LOAD DATA
       INCURSOR (C01)
       RESUME NO        REPLACE
       SHRLEVEL         NONE
       REUSE
       LOG              NO
       NOCOPYPEND
       SORTKEYS        (0)
       ENFORCE          NO
       ERRDDN           SYSERR
       MAPDDN           SYSMAP
       DISCARDDN        SYSDISC
           DISCARDS     0
 
       STATISTICS
           TABLE       (ALL)
           INDEX       (ALL)
           REPORT      (YES)
           UPDATE      (ALL)
           HISTORY     (ALL)
           FORCEROLLUP (YES)
 
       WORKDDN         (SYSUT1,SORTOUT)
 
  INTO TABLE <local site>.<target table owner>.<target table name>
/*
//