By Patrick Bossman and Paul McWilliams.
We've written about this subject before, in a previous blog entry
and in the Db2 migration documentation
, but it's worth repeating. If you're still running Db2 11 (which reaches its End of Support (EOS) in September 2020
) you're probably preparing to migrate to Db2 12, and you should rebind any plan or package that was last bound or rebound in Db2 9 or earlier, and you should do so very soon while you are still in Db2 11.
The goal of this best practice is to prepare Db2 for a successful migration by eliminating risks associated with very old packages and plans from causing problems with the migration. Bring the run-time structures to a version that is supported by the next release of Db2 before the migration, and perform that transition when risk-mitigating features like plan management are available to you.
If you migrate to Db2 12 without rebinding these older plans and packages, an automatic bind (or "autobind") occurs the first time that the application runs in Db2 12. If you allow these autobinds to occur, you could be facing outages or difficult to resolve performance regressions!
You can take action to prevent this problem, but don't wait until your Db2 12 migration window. A report in the DSNTIJPM pre-migration job
identifies the affected plans and packages. It is best to rebind any plan or package that DSNTIJPM identifies, well in advance of, even months before, your migration to Db2 12.
Then run your applications with the new run-time structures on Db2 11 for a while, so that you have sufficient time to address any performance issues that might occur, without risk of disrupting your Db2 12 migration window. The goal is to remove the operational and performance risks related to automatic binds from your migration window.
Rebinding the packages
For packages, the safest approach is to run explicit REBIND PACKAGE commands (or REBIND TRIGGER PACKAGE commands, for basic triggers) for all packages that DSNTIJPM identifies, with APREUSE(WARN) and PLANMGMT(EXTENDED).
- APREUSE(WARN) tells Db2 to try to reuse the existing access path if possible. It's not always possible, but the goal is to reduce risk. The reason for these rebinds is to refresh the run-time structures so that Db2 12 supports them, and you're not trying to get a new access path if you're satisfied with the existing performance.
- PLANMGMT(EXTENDED) tells Db2 to save the current run-time structures into the PREVIOUS (and ORIGINAL if empty) package copy slots. If you encounter application regressions after the rebind while still running on Db2 11, you can then execute a REBIND command with the SWITCH(PREVIOUS) option to restore the previous run-time structures and access path.
However, remember that the option to use REBIND(SWITCH) for these older packages is available only while you are running the package in Db2 11, because Db2 12 cannot use the older run-time structures. That's why it is so very important to do the rebinds early and give the applications some time to run in Db2 11 before the migration.
If the rebinds are not done in the Db2 11, Db2 12 must do an autobind because it cannot use the old run-time structures. The autobind deletes the old run-time structures and replaces them with new Db2 12 run-time structures, with the following results:
If a performance regression occurs, you cannot do a rebind to switch back to the previous package copy.
You cannot get the old access path back by falling back to Db2 11, because it did not generate those run-time structures. They came from a pre-Db2 10 release.
In data sharing coexistence, members still in Db2 11 cannot run a package that was bound in Db2 12, and they must do another autobind.
Rebinding the plans
For plans, you need not worry about APREUSE for PLANMGMT because those concepts do not exist for plans, which contain no run-time structures for SQL access paths. However, the structures in plans do require updating for Db2 12, and it is best to also rebind them in Db2 11, especially if you plan to run with co-existence in data sharing.
It's also worth noting that, unlike packages, no "break-in" is supported for rebinding plans, so you'll need to find a window for the rebinds, even if it means scheduling an outage. It's better to take any such outage when you can plan for and schedule it than to hit it in the middle of your migration process.
If plans identified by DSNTIJPM are not bound in Db2 11, upon migration to DB2 12, the first execution of the application in Db2 12 that attempts to use the plan causes an autobind. Because plans do not have break-in, and Db2 11 members might be successfully using the plan, it is also possible for the BIND PLAN operation to fail. The BIND PLAN operation takes an x-lock on the plan, new attempts to use the plan in Db2 11 are queued behind the x-lock for Db2 12 BIND PLAN. Therefore, it is possible to see application failures in both Db2 12 and Db2 11.
Additionally, if the plan autobind on Db2 12 succeeds, Db2 11 is not able to use the Db2 12 run-time structures. So the next execution of the plan on Db2 11 will drive an autobind, which might run into contention with Db2 12 application executions holding a lock on the plan.
The purpose of explicit rebind for plans in Db2 11 is to create run-time structures that both Db2 11 and Db2 12 can use, to remove these autobind concerns and risks from the migration to Db2 12. It is very important to rebind the plans DSNTIJPM identifies in Db2 11 before migrating to Db2 12, especially if you plan to run co-existence.
Patrick Bossman is and IBM Senior Technical Staff Member in Db2 for z/OS Development and Paul McWilliams is an Information Developer for Db2 for z/OS.#Db2Znews#Db2z/OS