June 14, 2011

Reducing the Risks of Rebind

Last year I wrote a post about plan stability in a skip migration scenario. As I later told a reader who was having little success finding more information on this topic, another--and perhaps more common--term for plan stability is access path stability. And in general, these terms fall under the umbrella of plan management.

In moving from one DB2 release to the next, customers need to rebind existing plans and packages to take advantage of new access paths and benefits related to new structures within DB2. However, because any access path change could negatively impact a business-critical transaction that had been running perfectly, many customers choose not to take the risk. In fact, some never use the rebind command, even though it means missing out on many key DB2 enhancements that come with each new release.

To mitigate the risks and make rebinding more appealing to customers, IBM introduced the plan management (PLANMGMT) parameter in DB2 9. The default in DB2 9 is OFF; however, with DB2 10 the default is now EXTENDED. The subsystem wide parameter is set through the system DSNZPARM using PLANMGMT in macro DSN6SPRM. This can also be found using the install clist on panel DSNTIPB.

The different settings are:

  • ( OFF )--No access paths are retained. Any access paths that existed before the BIND or REBIND command is issued with this option are purged. Previous or original copies are also purged.
  • ( BASIC )--The previous copy of a package is discarded. The current copy becomes the previous copy, and the incoming copy becomes the current copy. If an original copy of a package already exists, it remains available.
  • (EXTENDED)--The previous copy of a package is discarded. The current copy becomes the previous copy, and the original copy is managed as follows:

    •    If no original copy exists, the current copy is cloned to become the original.
    •    If an original copy exists, it is retained as the original.
    In each case, the incoming copy of a package becomes the new current copy.

You may have noticed that the plan management options all related to packages. In fact the PLANMGMT parameter isn't found on the REBIND PLAN. This option is only available on the REBIND PACKAGE and REBIND TRIGGER PACKAGE commands. This is because DB2 10 no longer supports DBRMs bound to a plan.

Have you had a chance to use Plan Management under DB2 9 or DB2 10? Have you taken advantage of switching to a previous version of a package because of a rebind getting a negative access path? Please share your experiences in Comments.