Parameters passed to the ADBTEP2 program

When Db2 Admin Tool generates the JCL to run ADBTEP2, parameters are generated automatically and passed to ADBTEP2.

Parameters that are passed in the PARMS field of the Db2 RUN statement

The following parameters are generated and passed to ADBTEP2 in the PARMS field of the Db2 RUN statement (See RUN (DSN) (Db2 12 for z/OS)):

MAXE(number)
Specifies the number of DSN commands that can fail before the batch job is terminated. number can have one of the following values:
-1
All errors are ignored. The batch job does not stop for any error.
0
No errors are allowed. The batch job stops on the first error. 0 is the default value.
1-99
The specified number of errors are ignored. The batch job stops on the next DSN command that fails. For example, if you specify 5, the batch job stops when the sixth DSN command fails.

Any failing DSN commands that are ignored are written to the ADBHOLD table. When the job ends, if any DSN commands failed, the restart action field in the checkpoint table contains an 'H' to indicate that the table contains held records. If RESTART(YES) is specified and the batch job ended with a return code of 0, the held records are reprocessed; otherwise, the job is restarted from the last recorded commit point. If RESTART(NO) is specified, the held records are purged and the job is restarted from the beginning.

You can specify the MAXE parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Maxerrors field.

RESTART (restart-value)
Specifies restart behavior. If you specify RESTART (with any value), you must also use the WORKLIST parameter.

restart-value can have one of the following values:

NO
Indicates that the job is not to be restarted, and execution starts with the first command. ADBTEP2 updates the checkpoint table, and the job can subsequently be restarted by using RESTART(YES).
YES
Indicates that the job is to be restarted from the last recorded commit point prior to a failure. RESTART(YES) is the default.

When execution begins, ADBTEP2 searches for a checkpoint record in the checkpoint table to determine where to reposition itself within the input. (The program skips committed commands.) When you specify RESTART(YES), ADBTEP2 does a basic check to ensure that the last command type that is held in the checkpoint record matches the command type that is to be attempted at restart. This check prevents an accidental reuse of a checkpoint against a different WSL.

Recommendation: Use caution when editing the input stream between ADBTEP2 failures. If the checkpoint record is not found, ADBTEP2 starts with the first command in the input stream.
FORCE
Indicates that the job is to be restarted from the last recorded commit point prior to a failure; however, ADBTEP2 does not check that the last command type that is held in the checkpoint record matches the command type that is to be attempted at restart. Because this basic check is not done, the restart point might be unintended and the results might be unpredictable. This check is done if you specify RESTART(YES).
Additionally, the check for the checkpoint record is not performed if either of the following conditions are true:
  • The COMMAND_RESTART column in the ADBCHKPT table has a value of 'S' upon the restart processing.
  • The checkpoint dialog Skip-Next line command is used.

You can specify the RESTART parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Restart field. If you specify No for ADBTEP2 restart, a RESTART(NO) parameter is generated for each ADBTEP2 job step.

WORKLIST(extended-name)
Specifies a unique identifier that is used in conjunction with the user ID of the submitter to provide the key for the checkpoint record.

The full format of extended-name is name.suffix, where name and suffix are each 1-8 alphanumeric characters. The separator must be a period (.). The suffix is optional, but if the suffix is omitted, the separator must also be omitted.

For jobs that Db2 Admin Tool generates, name is the same as the work statement list.

Examples:
  • WORKLIST(TEST1)
  • WORKLIST(TEST2.N00005)

The following parameters, which do not control restart functions, are also passed to ADBTEP2 in the PARMS field of the Db2 RUN statement.

ALIGN(align-value)
Specifies how the program is to be aligned. align-value can have one of the following values:
MID
Aligns output from the program to the center of the page. MID is the default value.
LHS
Aligns output from the program to the left-hand side of the page.
MIXED
NOMIXED
Specifies whether the input stream can contain single-byte character set (SBCS) data and double-byte character set (DBCS) data.
MIXED
Indicates that the input stream can contain a mixture of characters from a SBCS and a DBCS.
NOMIXED
Indicates that the input stream can contain only SBCS data. NOMIXED is the default.
PCACT(action)
Specifies the action to take when the job is to recover a change that was made through Change Management and pending changes exist for the same objects or related objects. action can have one of the following values:
CANCEL
Do not run the recover job.
SUPERSEDE
Run the recover job. The recover change supersedes the pending changes, and the pending changes are set to DEFINED status.
SQLTERM(c)
Specifies the character that terminates an SQL statement. c is the character. The default SQL terminator is the semicolon (;).
SSID(name)
A subsystem or group attachment name to use for running non-SQL commands or functions. This name should be the same as the name that is specified in the DSN SYSTEM command before the RUN command that invokes ADBTEP2. The SSID parameter is required if any non-SQL Db2 function is included in the input stream, such as a DSN command.

Parameters passed under the ADBTEPIN DD name

The following parameters are generated automatically and passed to ADBTEP2 in a data set with a DD name of ADBTEPIN:

ADVISORYAUTOREBUILD
Specifies whether ADBTEP2 initiates a REBUILD operation on an index when the object is in the ARBDP state.
YES
A REBUILD operation is attempted. However, if Run REORG/REBUILD = A is specified (on ALTER - Build Apply Job (ADBPALT) panel or Generate Analyze Job (ADB2C11A) panel) to generate an explicit REBUILD operation, ADVISORYAUTOREBUILD=NO is used. The NO value prevents an automatic REBUILD operation that duplicates the explicit REBUILD operation.
NO
A REBUILD operation is not attempted. NO is the default.

You can specify the ADVISORYAUTOREBUILD parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Advisory Auto Rebuild field.

Tip: To prevent ADBTEP2 from scheduling any automatic REBUILD operations, you must set AUTOREBUILD, ADVISORYAUTOREBUILD and STOGROUPAUTOREOR all to NO.
Related information:
ADVISORYAUTOREORG
Specifies whether ADBTEP2 initiates a REORG operation on a table space when the object is in the AREOR or AREO* state.
YES
A REORG operation is attempted. However, if Run REORG/REBUILD = A is specified (on ALTER - Build Apply Job (ADBPALT) panel or Generate Analyze Job (ADB2C11A) panel) to generate an explicit REORG operation, ADVISORYAUTOREORG=NO is used. The NO value prevents an automatic REORG operation that duplicates the explicit REORG operation.
NO
A REORG operation is not attempted. NO is the default.

You can specify the ADVISORYAUTOREORG parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Advisory Auto Reorg field.

Tip: To prevent ADBTEP2 from scheduling any automatic REORG operations, you must set AUTOREORG, ADVISORYAUTOREORG, and STOGROUPAUTOREOR all to NO.
Related information:
AUTOREBUILD
Specifies whether ADBTEP2 initiates a REBUILD operation on an index when the object is in the RPDB, RPDB*, or PSRBD state.
YES
A REBUILD operation is attempted. However, if Run REORG/REBUILD = M or Run REORG/REBUILD = A is specified (on ALTER - Build Apply Job (ADBPALT) panel or Generate Analyze Job (ADB2C11A) panel) to generate an explicit REBUILD operation,AUTOREBUILD=NO is used. The NO value prevents an automatic REBUILD operation that duplicates the explicit REBUILD operation.

YES is the default.

NO
A REBUILD operation is not attempted.

You can specify the AUTOREBUILD parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Auto Rebuild field.

Tip: To prevent ADBTEP2 from scheduling any automatic REBUILD operations, you must set AUTOREBUILD, ADVISORYAUTOREBUILD and STOGROUPAUTOREOR all to NO.
Related information:
AUTOREORG
Specifies whether ADBTEP2 initiates a REORG operation on a table space when the object is in the REORP state.
YES
A REORG operation is attempted. However, if Run REORG/REBUILD = M or Run REORG/REBUILD = A is specified (on ALTER - Build Apply Job (ADBPALT) panel or Generate Analyze Job (ADB2C11A) panel) to generate an explicit REORG operation, AUTOREORG=NO is used. The NO value prevents an automatic REORG operation that duplicates the explicit REORG operation.

YES is the default.

NO
A REORG operation is not attempted.

You can specify the AUTOREORG parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Auto Reorg field.

Tip: To prevent ADBTEP2 from scheduling any automatic REORG operations, you must set AUTOREORG, ADVISORYAUTOREORG, and STOGROUPAUTOREOR all to NO.
Related information:
STOGROUPAUTOREOR
Specifies whether ADBTEP2 initiates a REORG or REBUILD operation after an ALTER STOGROUP statement is executed for the table space or index.
YES
A REORG or REBUILD operation is attempted. However, if Run REORG/REBUILD = A is specified (on ALTER - Build Apply Job (ADBPALT) panel or Generate Analyze Job (ADB2C11A) panel) to generate an explicit REORG or REBUILD operation, STOGROUPAUTOREOR =NO is used. The NO value prevents an automatic REORG or REBUILD operation that duplicates the explicit REORG operation.
NO
A REORG or REBUILD is not attempted. NO is the default.

You can specify the STOGROUPAUTOREOR parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Auto Reorg/Rebuild after STOGROUP change field.

Tip: To prevent ADBTEP2 from scheduling any automatic REORG operations, you must set AUTOREORG, ADVISORYAUTOREORG, and STOGROUPAUTOREOR all to NO. To prevent ADBTEP2 from scheduling any automatic REBUILD operations, you must set AUTOREBUILD, ADVISORYAUTOREBUILD and STOGROUPAUTOREOR all to NO.
AC
Specifies whether ADBTEP2 automatically resolves CHECK-pending status.
YES
Any CHECK-pending states are automatically resolved by ADBTEP2.

ADBTEP2 tracks the following statements, utilities, and processes that can place an object in a CHECK-pending state:

  • ALTER TABLE … ADD FOREIGN KEY
  • ALTER TABLE …. ADD CONSTRAINT
  • LOAD REPLACE
  • LOAD ENFORCE(NO)
  • Recovery with the RECOVER utility to a point in time
  • An auto-check prior to running the COPY utility
  • An auto-check after a CHECKEND for the CHECK DATA utility
  • A final auto-check at the end of the SYSIN input stream
If one of these statements or processes is encountered, ADBTEP2 runs the CHECK DATA utility to remove the CHECK-pending state.
NO
Any CHECK-pending states are not automatically resolved by ADBTEP2. NO is the default.
Restriction: Db2 Admin Tool builds the CHECK DATA statement and all CHECK parameters that are used during auto-check processing. You cannot specify any other parameters.

You can specify the AC parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the AutoCheck field.

BINDERROR
Specifies how ADBTEP2 is to handle bind or rebind errors.
MAXE
Start of changeThe failing BIND or REBIND command is written to the ADBHOLD table. Whether ADBTEP2 continues to process the input stream is determined by the MAXE parameter. See MAXE(number). (The failing BIND or REBIND command is counted as an error by the MAXE parameter.)End of change
SAVE
The failing BIND or REBIND command is written to the ADBHOLD table, and ADBTEP2 continues to process the input stream.
IGNORE
The failing BIND or REBIND command is ignored. It is not written to the ADBHOLD table, and ADBTEP2 continues to process the input stream.

You can specify the BINDERROR parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the BindError field.

PENDINGCHANGESCHECK
Specifies whether ADBTEP2 checks for Db2 pending changes before executing a DROP statement. This check avoids losing those pending changes as part of the DROP action.
YES
ADBTEP2 checks for pending changes. If any exist, the DROP statement is not executed.
NO
ADBTEP2 does not check for pending changes. The DROP statement is executed regardless of whether pending changes exist.

You can specify the PENDINGCHANGESCHECK parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Check at DROP field.

Log DIAG
Specifies whether diagnostic messages are written to the ADBDIAG data set. IBM® Software Support can use this file to determine the cause of a failure.
YES
Messages are written to ADBDIAG.
NO
Messages are not written to ADBDIAG.

You can specify this value on the Batch Job Utility Parameters (ADB2UPA) panel in the Log DIAG field.

LOAD Summary Report
Specifies whether ADBTEP2 produces a LOAD summary report.
YES
The report is produced.
NO
The report is not produced.

You can specify this value on the Batch Job Utility Parameters (ADB2UPA) panel in the LOAD Summary Report field.

ADMUNLLOBXML
Specifies how ADBTEP2 processes UNLOAD statements when the object to be unloaded is an image copy of a table space and a table in that table space contains a LOB or XML column.
E
ADBTEP2 ends with an error.
U
The base object is unloaded instead.

You can specify ADMUNLLOBXML on the Batch Job Utility Parameters (ADB2UPA) panel in the LOB/XML IC Unload field.

ADMUNLNOIC
Specifies how ADBTEP2 processes UNLOAD statements when the object to be unloaded is an image copy of a table space and no image copy can be found.
E
ADBTEP2 ends with an error.
U
The base object is unloaded instead.

You can specify ADMUNLNOIC on the Batch Job Utility Parameters (ADB2UPA) panel in the Missing IC Unload field.

SPANNED
Controls whether the SPANNED YES option is added to the UNLOAD statement. SPANNED YES specifies that data is be unloaded into a data set with RECFM=VBS.
YES
SPANNED YES is added to the UNLOAD statement. The SPANNED YES clause is added only if the object being unloaded has a LOB or XML column.
NO
SPANNED YES is not added to the UNLOAD statement.

You can specify SPANNED on the Batch Job Utility Parameters (ADB2UPA) panel in the Spanned field.

Related information:
SQLFORMAT
Specifies how ADBTEP2 pre-processes SQL statements before passing them to Db2.
SQLCOMNT
ADBTEP2 does not discard SQL comments. ADBTEP2 automatically terminates each SQL comment with a line feed character (hex 25) unless the comment is already terminated by one or more line-formatting characters.

The SQLCOMNT option is suitable for all SQL, but it is intended primarily for SQL procedural language processing.

If you specify SQLCOMNT, you must add the following option manually to ADBTEPIN DD: 
SQLFORMAT = 'SQLCOMNT'
Start of changeTIMEOUT_RETRIESEnd of change
Start of changeSpecifies the maximum number of times that ADBTEP2 is to retry either executing the statement or restarting from the last checkpoint when one of the following conditions occurs:
  • For a timeout condition with SQLCODE -913 and reason code x'00C9008E' or a resource unavailable condition (SQLCODE -904 and reason code x'00C200EA'), ADBTEP2 retries executing the statement.
  • For a timeout condition with SQLCODE -911 and reason code x'00C9008E', ADBTEP2 tries restarting from the last checkpoint.
You can specify an integer value from 0 to 99. A value of 0 means that ADBTEP2 is not to attempt any retries. 0 is the default.

You can specify the TIMEOUT_RETRIES parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Timeout Retries field. You can also specify this parameter in the adbtep2_timeout_retries CM batch parameter.

End of change
Start of changeTIMEOUT_WAIT_TIMEEnd of change
Start of changeSpecifies the duration, in seconds, between retries by ADBTEP2. You can specify an integer value from 1 to 3600. The default is 120.

You can specify the TIMEOUT_WAIT_TIME parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Timeout Wait Time field. You can also specify this parameter in the adbtep2_timeout_wait_time CM batch parameter.

End of change
Start of changeRETRY_DEPRECATED_OBJEnd of change
Start of changeSpecifies whether ADBTEP2 is to retry an SQL statement at a lower Db2 function level to create a deprecated object.

This parameter applies only if you are running at Db2 12 function level 504 or higher.

YES
Retry the statement.

When the current application compatibility level is Db2 12 function level 504 or higher, and an SQL statement returns SQLCODE -20008 (because it is trying to create a deprecated object), ABDTEP2 takes the following actions:

  • Sets the APPLCOMPAT value to V12R1M503
  • Retries the statement
  • Changes APPLCOMPAT back to the previous value.

YES is the default.

NO
Do not retry the statement.

You can specify the RETRY_DEPRECATED_OBJ parameter on the Batch Job Utility Parameters (ADB2UPA) panel in the Retry Deprecated Obj field. You can also specify this parameter in the adbtep2_retry_deprecated_obj CM batch parameter.

End of change
TSACCESS
Controls whether the access state (RW/RO/UT/STOP) of the table space is preserved.
YES
At the end of ADBTEP2 execution, the table space is placed back in the same restrictive state that it was in before issuing the START DATABASE command.
YES has the following restrictions:
  • Partition-level access states are not preserved.
  • YES is valid for only one execution of ADBTEP2 unless you are restarting a failed job. The access state is not preserved if the job has multiple invocations of ADBTEP2.
  • The access state is not preserved when an APPLY job is created.
NO
The access state is not preserved. NO is the default.

You can specify TSACCESS on the Batch Job Utility Parameters (ADB2UPA) panel in the Preserve tablespace access state field.

Start of changeUSE_DSNTPSMPEnd of change
Start of changeSpecifies whether to call DSNTPSMP to create external SQL procedures from the generated DDL statements.
YES
DSNTPSMP is called.
NO
DSNTPSMP is not called. The CREATE PROCEDURE statement is executed, but you need to create the external load module and bind the package.

NO is the default value.

You can specify USE_DSNTPSMP on the Batch Job Utility Parameters (ADB2UPA) panel in the Use DSNTPSMP field.

End of change

Overriding work statement list (WSL) restart parameters

You can override the parameters that ADBTEP2 uses when performing a restart.

  1. Open Work Statement List Library (ADB2W1) panel (Option W > 1 from DB2 Administration Menu (ADB2) panel).
  2. Look for the WSL that you want to restart. The Restart column for this WSL should contain Y. Y indicates that checkpoint records exist for that work statement list.
  3. Type the R line command next to the WSL.
  4. On the Specify Restart Information panel, type the V line command to edit the restart information.
  5. Override the parameters as needed. You can override the following parameters:
    Decfloat Rounding Mode
    Specifies the system default action that is used for rounding decimal floating point values.
    Path
    Specifies the SQL path that is used when resolving unqualified function names, procedure names, data type names, and module object names in dynamically prepared SQL statements.
    Precision
    Sets the CURRENT PRECISION special register.
    Routine Version
    Sets the CURRENT ROUTINE VERSION special register.
    Rules
    Sets the CURRENT RULES special register.
    SCHEMA
    Specifies the CURRENT SCHEMA special register to use at the restart point.
    Server
    Specifies the location name of the current server.
    SQLID
    Specifies the current SQL ID.