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
//STEPLIBDD DISP=SHR,DSN=product.libraries
//DD DISP=SHR,DSN=DB2.DSNEXIT
//DD DISP=SHR,DSN=DB2.DSNLOAD
//ALPPRINT DD   SYSOUT=*
//SYSOUT    DD   SYSOUT=*
//ALPDUMPDD   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
//STEPLIBDD 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

Examples using JCL for EXECSQL

On this page