DB2 Catalog statistics and Access Path selection
It is commonly known that when new statistics are collected for an index or a tablespace object that static
SQL will not automatically be re-evaluated for a new DB2 access path. The DB2 catalog can be updated with new
statistics by the execution of either a CA Database Analyzer Extract collection or a CA Rapid Reorg utility
that utilizes the keyword UPDATE-CATSTATS.
Forcing DB2 to re-evaluate Access Path
One of the functions of CA Database Analyzer for DB2 for z/OS (PDA) is to create Action JCL (a.k.a. Object
Maintenance JCL). This can include the generation of a step to perform DB2 REBINDs using the US Utility
Code(s) and the default standard JCL model of MJUTLRPK (rebind package) and/or MJUTLRBN (rebind plan).
PDA only includes packages or plans related to the tablespace or index objects that were "SELECTED" for processing.
NOTE: Remember that PDA may only select a small subset of the objects that are included in the Extract when
Action Conditions thresholds are also used. (i.e. clusterratio < 90%). Only packages or plans for just those
objects are rebound.
DB2 package versions
DB2 can have several unique version strings associated with a given package name, which makes a package unique.
Users can manually specify exactly what they want the version string to be, or they can use the DB2 precompiler
option of VERSION(AUTO). If you code this, then you will have a version equal to the timestamp value assigned at
precompile time. (NOTE: for simplicity location and collection id are not discussed)
Controlling what packages and versions are in the REBIND
PDA uses the keyword PKG_VERSION in the PDA PARMLIB member to determine what versions of packages to include in
The keyword value of LATEST has caused some confusion. Users did not realize that this keyword value specifically
included only packages that had a version string value in a character timestamp format (i.e. 2003-07-11-18.104.22.1686426).
That is a version value that was created when using the DB2 precompiler option of VERSION(AUTO). Users had hoped
that PDA could find the latest bound package no matter what version string was used. It would seem that the BINDTIME
timestamp column in SYSPACKAGE would have been an easy way to determine this. Unfortunately, this column is updated
when you do a variety of REBINDs. For example, if REBIND PACKAGE somecoll.somepkg.(my_version_text) DISABLE(CICS) was
done, then BINDTIME is updated. A user would not want the package and version of my_version_text to be considered
the "latest" when PDA generated REBIND statements.
Users with both VERSION(AUTO) versioned packages and manually specified versioned packages would have to use the
ALL keyword value so all package versions would be generated in a REBIND. In this mixed environment, the downside
is that all "timestamped" versioned packages are generated. So if you used the VERSION(AUTO) for some of your
packages and normally keep several timestamped versions around for fallback purposes, then they would all be
generated in the REBIND statement produced by PDA. In actuality, however, only the latest "timestamped" version
package was being used.
Users can utilize additional keyword values to help with their REBIND package version generation in a mixed environment.
A value of LATEST2 will not only include the "latest" VERSION(AUTO) version but also includes manually specified
versions. LATEST3 is similar but specialized for cases where users only want to include the 'manually specified'
CA versions that are for the CA DB2 tools. These versions begin with "CA_DB2_TOOLS_" .
Comments in the PDA PARMLIB member have also been updated to help clarify the processing that each values results in.
PKG_VERSION (LATEST3) /* REBIND package syntax for */
/* %PKG and %PKGNAME symbolics. */
/* OFF = Do NOT generate */
/* versions in syntax */
/* (Default) */
/* ALL = Use all versions found */
/* LATEST = ONLY include latest */
/* version generated */
/* by DB2 precompile */
/* option VERSION(AUTO) */
/* LATEST2 = Same as LATEST but */
/* also include ALL pack- */
/* ages that are NOT in */
/* VERSION(AUTO) format */
/* LATEST3 = Same as LATEST but */
/* also include ALL pack- */
/* ages that are in CA */
/* DB2 product version */
/* formats CA_DB2_TOOLS_% */