#planmanagement (1)

By Terry Purcell

When DB2 9 for z/OS delivered plan management, the goal was to allow a backup of your static SQL packages such that you could “switch” back to the prior “good” access path if a new REBIND resulted in regression. Although optional in DB2 9, EXTENDED became the default in DB2 10 for z/OS – which allowed an “original” and a “previous” to be stored. The previous would be replaced each REBIND, but the original – that is populated upon first REBIND when plan management is enabled, and stays until you explicitly free it.

So herein lies the problem – that original copy was the “good” copy when you first enabled plan management. That original may now be stale and no longer representative of an access path you want to revert to in the event of a regression. Worse still, if that original is from prior to DB2 9, then it is unusable in DB2 11 and switching to it will result in an autobind occurring. Similarly if the original copy is now inactive, switching back will cause an autobind (unless you can use it as input to APREUSE – but that’s a topic for another day).

DB2 is not performing any automated housekeeping of that original copy. But once you are satisfied with the performance of the application after REBIND, then the need for that backup diminishes.

In DB2 10 & 11, you can free both the previous and original, but you cannot selectively free one and not the other. Again though, if the current package performance is satisfactory and has been for some time, then freeing both will not cause harm and allows the next REBIND to repopulate both the previous and original.

To free the previous and original you issue the FREE PACKAGE command with PLANMGMTSCOPE(INACTIVE) option such as:


If you are looking for a “bare minimum” recommendation as when to free the inactive (previous and original) copies? Right before or during the migration from one release to the next is a good time, such that the first REBIND in the new release will repopulate the previous and original. For example, upon migration from DB2 10 to 11, as a cleanup step prior to or during migration, freeing the inactive copies means that upon first rebind in DB2 11, the prior DB2 10 copies will become the previous and original. This assumes you are rebinding at least once in each new release – which is also the minimum REBIND recommendation from DB2.

If you are rebinding more frequently, then consider cleaning up those inactive copies more frequently also. I am not suggesting that every time you rebind you also clean up those inactive copies. Every second, third or fourth rebind is more reasonable, depending on the frequency of rebinds. Remember though, issue the FREE before the next REBIND, and not immediately after, since that would erase your history before you had a chance to execute the package’s new access path.

SYSIBM.SYSPACKAGE and SYSIBM.SYSPACKCOPY both have the BINDTIME to allow you to easily determine the comparative age of the current (in SYSPACKAGE) vs the inactive copies (in SYSPACKCOPY). This will highlight how old and thus potentially useful or useless that original copy is for each package.

There are enhancements coming in this space in the next release of DB2, but cleanup of old plan management copies is an important step in DB2 10 and 11 for z/OS to ensure that when needed, you have a useful copy to revert to.

Read more…