by David Simpson & Linda Claussen

There has been quite a bit of attention paid to the access path stability features of DB2 for z/OS since they first appeared in DB2 9.  Enhancements have followed in each subsequent release.  This article will discuss these features.

A very common use of these features is to stabilize performance across release migrations.  It is desirable to rebind application packages after a migration.  Making use of these features can decrease the risk of access path regression (SQL performance problems) when things are bound on the new release.

For static SQL, the stability feature are implemented with three bind options:

• PLANMGMT
• APREUSE
• APCOMPARE

Plan Management (PLANMGMT)

The PLANMGMT option introduced in DB2 9 applies only to REBINDs and offers a way to preserve multiple package copies and allows you to switch back to a previous copy of the bound package. If the access path of the current package is not as efficient, the administrator may switch back to a previous version of the package with a simple REBIND SWITCH command to the old copy of the package. Keep in mind that package copies are different from package versions.  For those who already use package versioning, each version of a package may have up to three copies for stability.

DB2 9 introduced the DSNZPARM PLANMGMT, and PLANMGMT and SWITCH options for REBIND PACKAGE.  A value of BASIC or EXTENDED for PLANMGMT results in slightly altered behavior. REBIND automatically saves copies of all pertinent records from catalog tables and directory that pertain to the existing package. In the event of a performance regression, these records can be restored and you can fall back to the saved copies by executing a REBIND SWITCH using the previous or original.

There are three options for PLANMGMT:

• NONE – no previous copies are saved and the new runtime structures are generated.
• BASIC – a new copy of the package is produced and the existing runtime structures are preserved as the “previous” copy.
• EXTENDED – a new copy of the package is produced and the existing runtime structures are preserved as the “previous” copy. Also, the “original” (or oldest available) copy of the package is preserved.  Any REBINDs between the original and most recent previous are discarded.

Prior to DB2 10, the SYSPACKAGE catalog table contains only the information about the current version. No information was available for previous or original copies. Users must use SWITCH(PREVIOUS) or SWITCH(ORIGINAL) to reveal the SYSPACKAGE data for that version.  DB2 10 introduced the SYSPACKCOPY catalog table which holds metadata for the old package copies (that is, PREVIOUS and ORIGINAL). This table contains many of the columns of SYSPACKAGE.

This feature helped react to a situation where access path regression occurred after a DB2 upgrade or other significant event.  It is important to note, however, that when a package is marked invalid that this applies to ALL the copies.  You will no longer be able to SWITCH to a previous copy after invalidation has occurred.

DB2 10 also introduced the APRETAINDUP option which determines whether or not DB2 retains an old package copy when access paths of the old copy are identical to the incoming copy. This option only applies when PLANMGMT(BASIC) or PLANMGMT(EXTENDED) are in effect.

Access Path Reuse (APREUSE)

The APREUSE bind option was introduced in DB2 10 may be used on both BIND and REBIND.  This feature attempts to force the package to use the same access paths as the prior bind/rebind.  There are three possible settings:

• NONE is the default and will optimize every statement from scratch.
• ERROR will indicate that the bind or rebind should fail if the prior access paths are not possible. The wording here is a bit confusing, but this is the option to use if you do not want to tolerate any new access paths.  Essentially the access paths from the previous package are passed in as hints to the optimizer.
• WARN is a new option in DB2 11 that works like ERROR but will not fail the bind (or rebind) if previous access paths are not possible. Instead new ones will be determined and warnings issued.  This is done at the statement level so that only the failing statements will be re-optimized.  For a BIND (as opposed to REBIND) DB2 will examine the access paths of the version being replaced OR the most recent version of the package if the bind is creating a new version.

The details of differences discovered by APREUSE(WARN) and APCOMPARE(WARN) will be documented in the REMARKS column of the PLAN_TABLE.  Reason codes are documented under SQLCODE +395 in the Codes manual.

When APREUSE(ERROR) causes the bind to fail it may be helpful to run another bind using APCOMPARE(WARN) EXPLAIN(ONLY) to help determine the problem.  Details may be found in the PLAN_TABLE in the remarks column. 

There is an interesting exception to the above when using APREUSE(WARN).  If the only change to an access path is an increase in the number of matched columns on the same index, then the newer path is allowed and no warnings are issued.  This is viewed as a universally positive change.

Comparing Access Paths (APCOMPARE)

Ah, the forgotten stability bind option.  APCOMPARE (introduced in DB2 10) will allow you to compare access paths from the previous package to the new ones being generated.  This option is valid for both BIND and REBIND. Acceptable values for this option are:

• NONE is the default and will do no comparisons.
• WARN will issue warnings if access paths change and allow the bind to complete successfully.
• ERROR will fail the bind if any differences are detected in the new access paths.

When APCOMPARE(WARN) is used, the differences are noted in the REMARKS column of the PLAN_TABLE.  This is also a good option to consider placing in your normal change control binds to alert you when performance might change.

Conclusion

PLANMGMT(EXTENDED) became default behavior in DB2 10.  Allowing this behavior is recommended as a failsafe to any actual regression that may occur.  APRETAINDUP(NO) may be used to help reduce the size of the catalog and directory for those packages where no actual change occurs.

Administrators should evaluate the use of APREUSE and APCOMPARE for regular change control as well as through a migration.  APCOMPARE(WARN) is a great way to notify you that an access path is changing when programs are bound in production.

For release migrations, I actually like to use APCOMPARE(ERROR) to help evaluate the access paths that DB2 would like to change.  You then have a list of packages and statements that would change on the new release.  You can then rebind the failing packages with APREUSE(ERROR) or (WARN) to prevent changes, OR if you determine the changes would be beneficial, rebind without any stability options.

David Simpson
Themis Training
dsimpson@themisinc.com

Linda Claussen
Themis Training
lclaussen@themisinc.com