Example 2: JCL for SQL code handling with EXECSQL
You can use EXECSQL to define responses to SQL codes in the product’s batch interface. To take this action, insert the SQLCODES DD statement directly in your JCL.
For information about the product’s default SQLCODES rules, see the relevant table in Response.
The format of an individual SQLCODES rule is
statement_type (condition) = response
In this format,
- statement_type represents a value listed in Statement-type.
- condition represents a value listed in Condition.
- response represents a value listed in Response.
The following figure shows JCL that performs basic SQL code handling with EXECSQL (using an SQLCODES DD statement). This example:
- Includes three tables
- Contains one job that generates SQL statements and translates the table names during generation
Contains a second job to execute the SQL against the target tables
The second job uses an SQLCODES DD statement to define special handling for the following SQL codes:
- For SQL code -803, the product issues a warning whenever it executes an INSERT statement that encounters a duplicate row.
- For SQL code +100, the product ignores any UPDATE statements that do not affect any rows in the target table.
//* 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 2013-01-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 |
|---|---|---|
Generated High-speed Apply JCL | This example uses High-speed Apply configuration parameters that enable you to respond more productively to SQL codes. | |
Independently coded High-speed Apply JCL | This example:
|
Related topic