Comparing old and new packages in batch
COMPARE compares the access paths of two versions of a bound package.
You can use the sample JCL member, ssidJCMP, in SANLJCL to perform a COMPARE. The output reports only the changed statements.
Input commands
- ACTION
- Valid values for the ACTION command are COMPARE and END. Each COMPARE that is run must start with ACTION COMPARE and end with ACTION END. Multiple COMPAREs may be run in one JCL execution, but each one must begin and end with the ACTION command. This command is required.
- PKGNAME
-
The value of the PKGNAME command indicates the name of the package to be compared. The value must be enclosed in quotes and may contain a wild card character such as "ABC*". This command is required.
- COLLID
- The value of the COLLID command indicates the collection id of the current package to be
compared. 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.
- COLLIDP
- The value of the COLLIDP command indicates the collection id of the previous package to be
compared. 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 optional.
- 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.
- COMP2PRV
- The value lets you specify whether to compare the most recent access paths against the
previous set of access paths, or against the previous version ID:
- N: Do not compare most recent access paths against the previous access paths
- Y: Compare most recent access paths against the previous access paths. The current version is set to 0, the previous version to-1, and the previous SQLID to the current SQLID.
- P: Compare most recent access paths against the access paths in the previous bound package by version ID.
- FCSVCOMP
- The value of this field indicates whether to create a CSV file for the COMPARE report. Valid values are Y, and N. The default value is N ((do not create CSV file).
- FREPORTC
- The value shows what type of the report Delta or Summary was chosen.
- DELIMC
- 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 (;).
- SHOINSDEL
- The value shows details of inserted/deleted statements. Valid values are N, Y.
- STMTMATCH
- The value of the STMTMATCH command indicates the method used to match the statements between package versions. The valid values are 1, Q, and S. A value of 1 matches by table and query type. 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.
- PREVSQLID
- The value of the PREVSQLID command indicates the owner of the explain tables that hold the access path information for the previous package. The value must be enclosed in quotes. This command is optional and defaults to the current SQLID if not specified.
- CURVERS
- The value of the CURVERS command indicates the version of the package that is to be used as the current package. The value may be a quoted version id or it may be a relative version number, with 0 as the most recent version, -1 as the next older version, etc. This command is optional and defaults to 0 if not specified.
- PREVVERS
- The value of the PREVVERS command indicates the version of the package that is to be used as the previous package. The value may be a quoted version id or it may be a relative version number, with 0 as the most recent version, -1 as the next older version, etc. This command is optional and defaults to -1 if not specified.
- 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 (No).
- 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.
- STMTMATCH
- The value of the STMTMATCH command indicates the method used to match the statements between package versions. The valid values are 1, Q, and S. A value of 1 matches by table and query type. 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). 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 (Yes) to see all changes, or N (No) 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).
- 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.