Examples using JCL for EXECSQL

The following figure shows JCL that executes SQL statements by using an EXECSQL statement in a separate job.

This example:

Includes three tables
Contains one job that generates SQL statements and translates the table names during generation (by including the SQLXLAT DD statement)
Contains a second job to execute the SQL against the target tables (by using an EXECSQL statement)

For an example that uses the Log Master online interface to execute SQL statements with EXECSQL, see Adding an execute SQL or DDL step to a work ID.

//**********************************************************************
//*     BMC AMI Log Master for Db2 V12.01.00
//*     DSN: 'DB2DBA.JCL.EXSQL(ESQL$E7)'
//*     GENERATED BY USER: DB2DBA3
//**********************************************************************
//*              >>>>> GENERATE MIGRATE SQL <<<<<
//**********************************************************************
//MIGSQL1   EXEC PGM=ALPMAIN,
//          PARM='DGA1,,MSGLEVEL(2),ALPOPTS(ALP$OPTS)',REGION=0M
//STEPLIB	DD DISP=SHR,DSN=product.libraries
//			DD DISP=SHR,DSN=DB2.DSNEXIT
//			DD DISP=SHR,DSN=DB2.DSNLOAD
//ALPPRINT  DD   SYSOUT=*
//SYSOUT    DD   SYSOUT=*
//ALPDUMP	DD   SYSOUT=*
//SYSUDUMP  DD   SYSOUT=*
//SYSIN     DD *,DLM=##
 /* DB2DBA.$$WORKID0001                                      */
 /* DB2DBA 2020-08-18 GENERATE MIGRATE SQL                   */
  OPTION
     FILTERREL AND EXECUTION MODE CURRENT
     DATEFMT ISO
  WORKID DB2DBA.$$WORKID0001
     DESC 'DB2DBA GENERATE MIGRATE SQL'

  LOGSCAN
     SQL MIGRATE DATASET
            DB2DBA.DEV.OUTSQ1 SHR
         TEMPLATE
            DB2DBA.DEV.OUTSQTE1 SHR

     FROM  DATE(08/18/2020) TIME(09.22.15.000000)
     TO    DATE(08/18/2020) TIME(09:23:45.000000)
     WHERE
        TABLE NAME IN (DB2DBADB.DB2DBAT1,
                       DB2DBADB.DB2DBAT3,
                       DB2DBADB.DB2DBAT5)
##
//SQLXLAT DD *
  TABLE DB2DBADB.DB2DBAT1 -> DB2DBADB.MIGSQLT1
  TABLE DB2DBADB.DB2DBAT3 -> DB2DBADB.MIGSQLT3
  TABLE DB2DBADB.DB2DBAT5 -> DB2DBADB.MIGSQLT5

//**********************************************************************
//*     BMC AMI Log Master for Db2 V12.01.00
//*     DSN: 'DB2DBA.JCL.EXSQL(ESQL$E8)'
//*     GENERATED BY USER: DB2DBA3
//**********************************************************************
//*              >>>>> EXECUTE MIGRATE SQL <<<<<
//**********************************************************************
//EXESQL1   EXEC PGM=ALPMAIN,
//          PARM='DGA1,,MSGLEVEL(2),ALPOPTS(ALP$OPTS)',REGION=0M
//STEPLIB	DD DISP=SHR,DSN=product.libraries
//			DD DISP=SHR,DSN=DB2.DSNEXIT
//			DD DISP=SHR,DSN=DB2.DSNLOAD
//ALPPRINT  DD   SYSOUT=*
//SQLPRINT  DD   SYSOUT=*
//SYSOUT    DD   SYSOUT=*
//SYSERR    DD   SYSOUT=*
//SYSUDUMP  DD   SYSOUT=*
//SYSIN     DD *,DLM=##
 /* DB2DBA.$$WORKID0002 */
 /* DB2DBA 2020-08-18 EXECUTE GENERATED SQL*/
  OPTION
     FILTERREL AND EXECUTION MODE CURRENT
     DATEFMT ISO
  WORKID DB2DBA.$$WORKID0002
     DESC 'DB2DBA EXECUTE GENERATED SQL'

  EXECSQL DB2DBA.DEV.OUTSQ1 SQLPRINT (ALL)
##
//SQLCODES DD *
  INSERT (-803) = WARN 
  UPDATE (+100) = IGNORE

The following table compares this example using the EXECSQL method with examples that perform the same basic tasks using other methods.

Example figure	Method	Differences
The JCL sample in Example 2: High-speed Apply JCL generated by the online interface	Generated High-speed Apply JCL	Uses High-speed Apply to translate table names during execution Enables multi-threaded execution (multiple agents)
The JCL sample in Example using independently coded High-speed Apply JCL	Independently coded High-speed Apply JCL	Uses High-speed Apply to translate table names Enables multi-threaded execution Enables the product’s restart capability

Related topic

Running SQL

Examples using JCL for EXECSQL

Comments