Bookmark and Share

Recent Posts

Extended Plan Management and Migration

May 15, 2018

I’ve written often about plan management over the years. Most recently I wrote about a study of how customers are setting Db2 zparm.
During this study it was noted that 55 percent of customers use the extended plan management option, (EXTENDED). In the previous post I broke down the BASIC and EXTENDED settings:
  • (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. It's critical to keep this in mind as you migrate toward Db2 12.
Because the package created under Version 9 cannot run under Db2 12, you must REBIND the package under a supported version of Db2. It's considered best practice to REBIND the package under the newer release. So with Db2 10, the Db2 9 package should be re-bound as V10. At this point, start using PLAN MANAGEMENT EXTENDED, which will store a V10 package as the original. Once you're ready to proceed to Db2 11, REBIND the Db2 10 package as Db2 11. Doing this places the "original" DB2 10 package as "previous." Then when you go to Db2 12, REBIND once more. This will give you three copies: the current (Db2 12), the previous (Db2 11) and the original (Db2 10).
If you choose, at this point you can clear out the original using the FREE PACKAGE option, which is updated in Db2 12. In Db2 11 and earlier versions, the FREE PACKAGE PLANMGMTSCOPE(INACTIVE) command allows you to free the original copy. In the process though, it would also remove the previous copy, so customers generally avoided this option. In Db2 12, the FREE PACKAGE PLANMGMTSCOPE(ORIGINAL) command removes the original, but leaves the previous version intact.
This step by step approach to migration is needed to ensure that you receive all the performance benefits provided with each new Db2 release. Again though, do not keep an "original" package that's earlier than Db2 10.
The IBM Knowledge Center has more about FREE PACKAGE and the PLANMGMGSCOPE options.
If you're already following this practice, please share your experience in comments.

Posted May 15, 2018 | Permalink

comments powered by Disqus