February 03, 2009

Taking Hints Gets Easier

Implementing new features and functions that improve SQL performance has been a long-time challenge for IBM, because these enhancements require a rebind of all the packages in the catalog. For years many customers would apply maintenance and upgrades, but refuse to rebind out of fear that changes to the access paths would cause well-running packages to be ridden with increased CPU and/or elapsed times. 

To help give some security to the situation, IBM delivered hints with DB2 version 8. The idea with hints is to provide a manual override to the access path chosen by the optimizer.  So if a rebind after some maintenance or upgrade causes the optimizer to choose a poor access path, you can revert to the previous path.

Many DBAs frown on this technique because over time the optimizer keeps improving, delivering efficient new access paths that you'd never use if you relied on hints. Others are reluctant to deal with the technical requirements. Hints can be complicated to implement. Some vendors have even developed products to help you implement and manage hints.

To address the technical roadblocks of rebinding, IBM has introduced, via maintenance APAR PK52523, Plan Stability with DB2 9 for z/OS.

Plan Stability makes it very easy to use the REBIND command to fall back to a previous copy of the package access path. The REBIND plan management options are:

  • PLANMGMT(OFF) -- No change to existing behavior. A package continues to have one active copy
  • PLANMGMT(BASIC) -- A package has one active copy, and one additional old copy (PREVIOUS) is preserved.
  • PLANMGMT(EXTENDED) -- A package has one active copy, and two additional old copies (PREVIOUS and ORIGINAL) are preserved.

Using the REBIND SWITCH parameter, you can switch back to a previous copy or to the package's original copy:

  • REBIND SWITCH(previous) – The previous copy is activated
  • REBIND SWITCH(original) – The original copy is activated

So now with DB2 V9 you don't have to fear access path changes spawning performance nightmares. Once a performance problem is spotted, it just takes a few seconds to rebind and switch back to a previous or original copy of the package access path.

(Note: For much more on hints, check out this presentation by IBM's Patrick Bossman.)