Testing current access paths for packages in batch

TEST compares the access paths for a new DBRM or stored procedure instance to the access paths of a previously bound package, using either the package SQL statements from a DBRM library or from the catalog.

TEST requires input from explain table data saved in your explain tables when the package was last bound. You can use the sample JCL member, ssidJTST, in SANLJCL to perform a TEST. The output reports only the changed statements.

There are two popular use cases for TEST:

First, during a migration from one Db2 level to the next, you can TEST bound packages to detect likely access path and cost changes. Use of wildcards in the PKGNAME should help to process multiple packages in one run.
Second, before binding a new instance of a package during an application change, you can test just that package to determine if an access path change is likely, and the effect of that change on the performance and cost of package execution.

Input commands

ACTION: Valid values for the ACTION command are TEST and END. Each TEST that is run must start with ACTION TEST and end with ACTION END. Multiple TESTs may be run in one JCL execution, but each one must begin and end with the ACTION command. This command is required.

COLLID: The value of the COLLID command indicates the collection id of the current package to be tested. The value must be enclosed in quotes and may contain a wild card character such as "ABC*". If a wild card character is used, both current and previous collection IDs must be identical. This command is required.

CURSQLID: The value of the CURSQLID command indicates the owner of the explain tables that hold the access path information for the current package. The value must be enclosed in quotes. This command is optional and defaults to the current SQLID if not specified.

DBRMLIB: The value of the DBRMLIB indicates a fully qualified partitioned data set that contains the DBRM to be used to obtain the statements for testing. The value must be enclosed in quotes. This command is only required if FROMCATLG has a value of N.

EXPLTYP

The value of the EXPLTYP command indicates the type of explain that will be used. The choices are D (Dynamic) or X (eXplain only).

If D is entered, the explain is performed using dynamic path selection.
If X is entered, the explain is performed using a bind with the EXPLAIN(ONLY) option. This option uses more system resources, but produces a more accurate access path prediction.

EXPSQLID: The value of the EXPSQLID command indicates the name of SQLID to run EXPLAIN on the specified packages. If Generic ID is required, the value must be "+OFF+". Otherwise the value may be a quoted SQLID. This command is optional and defaults to the current SQLID if not specified.

FROMCATLG: The value of the FROMCATLG command indicates where the statements used for comparing to the package will be obtained. The valid values for the FROMCATLG command are C, N, and Y. A value of N indicates that the statements will be extracted from a DBRM member in the DBRMLIB library. A value of Y indicates that the statements will be extracted from the catalog. A value of C indicates that the statements will conditionally be extracted from a DBRM member in the DBRMLIB library if it is found, otherwise the statements will be extracted from the catalog. If a value of N is used, the DBRMLIB command is required. This command is optional and defaults to C if not specified.

FREPORTT: The value shows what type of the report Delta or Summary was chosen.

FCSVTEST: The value of this field indicates whether to create a CSV file for the TEST report. Valid values are Y and N. The default value is N (do not create CSV file).

DELIMT: The value of this field indicates the column delimiter for the CSV file. The valid values are semicolon (;) or a comma (,). The default value is semicolon (;).

DEGREE: The DEGREE option determines whether to attempt to run a query using parallel processing to maximize performance.

BEFORE: Show only packages bound before (timestamp): Specify a partial Db2 timestamp in the format yyyymmddHHMMSS to show packages bound packages that were bound before that time. Leave the field blank to select all packages bound before the current time.

RETCMCODE: This option allows to set return code for Batch job to 5 if the access path was changed. Valid values are Y and N. The default is N.

PKGNAME: The value of the PKGNAME command indicates the name of the package to be tested. The value must be enclosed in quotes and may contain a wild card character such as "ABC*". This command is required.

STMTMATCH: The value of the STMTMATCH command indicates the method used to match the statements. The valid values are 1, 2, Q, and S. A value of 1 matches by table and query type. A value of 2 matches by table, query type, and statement text. A value of Q matches by query number. A value of S matches by the sequence of the statements. This command is optional and defaults to 1 if not specified.

SHOPCST

Processes packages with a different total cost (all statements in the package). Valid values are

N: no filtering
Y: TOTAL_COST has changed
- (minus sign): TOTAL_COST has decreased
+ (plus sign): TOTAL_COST has increased

SHOACCPTH: The value of the RETCMCODE lets you see records that have changes in their access path, even if they do exceed cost limits. Valid values are: Y to see all changes, or N to filter out changes that exceed cost limits.

SHOSCST

Statement cost filter: Processes statements with a different total cost. Valid values are:

N: no filtering
Y: TOTAL_COST has changed
- (minus sign): TOTAL_COST has decreased
+ (TOTAL_COST has increased)

SHOPINVW: Show invalid packages: Determines whether a warning message will be shown for packages flagged as INVALID in SYSIBM.SYSPACKAGE. An invalid package will be skipped, and if this field is set to Y, a warning message will be issued in the report for all invalid packages. For example: ANL0213W Package collid.pkgname is flagged as INVALID by Db2. Rebind is recommended.

SHOPCTCHG: Minimum percent cost change to display: Minimum package/statement total cost the compare will process. If both Package and Statement cost filters are specified, the threshold value will first be used for Package total cost and then for statements inside the package. If both cost filters are set to N, this option has no effect.

SHOPCTCH: Show percent change: This option shows all numeric fields (ACCESS_DEGREE, JOIN_DEGREE, MATCHCOLS, PROCMS, PROCSU and TOTAL_COST) to be shown with an additional percent change display. The default is N (no display).

APREUSE: The APREUSE option specifies whether Db2 tries to reuse previous access paths for SQL statements in a package. Db2 uses information about the previous access paths from the directory to create a hint.

APREUSESO: The APREUSESOURCE bind option, when used with the APREUSE bind option, specifies which package copy to use for EXPLAIN information for access path reuse processing. You can use the APREUSESOURCE bind option to specify which package copy is used for access path reuse when the APREUSE option is specified.