Example 1: SQL code handling with independently coded High-speed Apply JCL
- Contains a separate job that uses High-speed Apply to execute SQL against the target tables. The High-speed Apply job defines:
- A separate conflict file where deferred statements are written for subsequent processing, examination, or research
- Special handling for SQL code -803 to write any INSERT statements that encounter a duplicate row into a separate conflict file for subsequent processing
- Special handling for SQL code +100 to issue an error message and terminate processing on any DELETE statements, and to write any UPDATE statements into a separate conflict file for examination and research
JCL example, SQL code handling: independently coded JCL
//*BMC AMI Log Master for Db2
//* DSN: 'DB2DBA.JCL.EXSQL(ESQL$E11)
//*GENERATED BY USER: DB2DBA3
//**********************************************************************************
//* >>>>> GENERATE MIGRATE SQL <<<<<
//**********************************************************************************
//MIGSQL1EXEC 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
//ALPPRINTDDSYSOUT=*
//SYSOUTDDSYSOUT=*
//ALPDUMPDDSYSOUT=*
//SYSUDUMPDDSYSOUT=*
//SYSINDD *,DLM=##
SYSIN Syntax Omitted. See Previous Figures for SYSIN Syntax.
##
___________________________________________________________________________________________
//*****************************************************************************************
//*High-Speed Apply V13.1.00
//* DSN: 'DB2DBA.JCL.EXSQL(ESQL$E12)
//*GENERATED BY USER: DB2DBA3
//**********************************************************************************
//* >>>>> EXECUTE MIGRATE SQL <<<<<
//**********************************************************************************
//EXESQL1EXEC PGM=APTBMAIN,PARM='SSID=DGA1',REGION=0M
//STEPLIBDD DISP=SHR,DSN=product.libraries
//DD DISP=SHR,DSN=DB2.DSNEXIT
//DD DISP=SHR,DSN=DB2.DSNLOAD
//APTERRDD SYSOUT=*
//APTPRINTDD SYSOUT=*
//SYSOUTDD SYSOUT=*
//SYSPRINTDD SYSOUT=*
//SYSTERMDD SYSOUT=*
//APTDUMPDD SYSOUT=*
//SYSINDD *,DLM=##
/StartUp/
InputType=SQL
FileName=DB2DBA.DEV.OUTSQ1
/Restart/
RestartType=New
RestartID=DB2DBA
/Agent/
MaxAgents=5
/ConflictFile/}High-Speed Apply configuration
FileNameModel=DB2DBA.DEFER.D&DATE..T&TIME. }parameters to define conflict
SingleFile=Yes}file for deferred statements
/InsertConflict/}Configuration parameters
Code=803}for SQL code-803
Action=DeferStatement}with
Action=Continue}INSERT statements
/UpdateConflict/}Configuration
Code=100}parameters for SQL
Action=DeferStatement}code +100 with
Action=Continue}UPDATE statements
/DeleteConflict/}Configuration parameters for
Code=100}SQL code +100 with
Action=Abort}DELETE statements
/ObjectMap/
SourceTable=DB2DBADB.DB2DBAT1
TargetTable=DB2DBADB.MIGSQLT1
SourceTable=DB2DBADB.DB2DBAT3
TargetTable=DB2DBADB.MIGSQLT3
SourceTable=DB2DBADB.DB2DBAT5
TargetTable=DB2DBADB.MIGSOLT5
##
The following table compares this example using the independently coded High-speed Apply JCL method with examples that perform the same basic tasks using other methods:
Example figure | Method | Differences |
|---|---|---|
JCL sample in Example-2-JCL-for-SQL-code-handling-with-EXECSQL | EXECSQL | In this example, the SQLCODES rules:
The rules cannot defer statements because EXECSQL does not support conflict files. (You can examine the statements and results of SQL execution by using the file defined by the SQLPRINT DD statement.) |
Generated High-speed Apply JCL | This example combines SQL code responses for all statement types. The online interface does not generate the High-speed Apply configuration parameters for separate responses based on statement type. |