Rebind old plans and packages in Db2 11 to avoid disruptive autobinds in Db2 12

By taking appropriate actions in Db2 11 well before migration to Db2 12, you can minimize the risk of disruptive automatic binds of application plans and packages during and after migration to Db2 12.

Before you begin

Identify plans and packages that are subject to autobind at migration to Db2 12 because they were last bound in a release earlier than DB2 10. To do so, run job DSNTIJPM and examine the premigration reports about package copies and plans that are not supported after migration to Db2 12. For more information, see Run premigration queries (DSNTIJPM).

Important: Start of change

Apply APAR PI87675 to eliminate the possibility of repeating automatic remigration binds during release coexistence. End of change

About this task

Tip: Do this task well in advance (months if possible) before migration to Db2 12, so that you have sufficient time to run the applications and address any performance problems that might occur after the rebinds. The goal is to remove the operational and performance risks related to automatic bind from the Db2 12 migration window.

Migration-related automatic binds (also called autobinds) occur in Db2 12 because it cannot use runtime structures from a plan or package that was last bound in a release earlier than DB2 10. Plans and packages that were bound in Db2 11 can run in Db2 12, without the risk of migration-related autobinds. However, plans and packages that are bound in Db2 12 cannot run on Db2 11 members without an autobind in Db2 11.

If the autobinds are allowed to occur, they are inevitably disruptive for rolling online migrations, or any time that different releases coexist in data sharing environments. During and after migration, such autobinds can result in costly problems, including performance regression and even application outages.

Autobinds occur the first time that any plan or package runs in Db2 12, which means that the autobinds, and resulting problems and application outages, can occur well after a migration window. That is, such problems and resulting outages can occur any time in the first impression window in Db2 12, which is any time until the plan or package first runs in Db2 12.

The best approach is to bind all packages and plans that might be subject to migration-related autobinds in Db2 11 with good performance before you start the migration to Db2 12. Doing so reduces the risk of the following problems that can result from autobinds:

Application failures because of contention between releases. For any migration that brings into release coexistence, autobinds might fail for plans or packages that are in use by a Db2 11 member, causing applications to fail on the Db2 12 member.
Successful autobinds on Db2 12 in a coexistence environment also create a package or plan that Db2 11 members cannot run without another autobind. The autobind might also fail if the plan or package is in use by a Db2 12 member.
Application failures, when autobinds fail for access control authorization exit users because the authorization ID for the autobind has insufficient privileges, compared to the owner of the package or the plan.
Difficult to resolve performance regressions. For packages, a migration-related autobind also destroys the current package. If the autorebind succeeds but introduces an access path regression, REBIND SWITCH is not possible. Any performance regression that occurs in Db2 12 might also occur in Db2 11.

Procedure

To avoid autobinds during or after the Db2 12 migration process, take the following actions. For best results, complete these actions well before migration to Db2 12 (months if possible) so that you have sufficient time to run the applications and resolve any performance issues that might occur after the rebinds.

In Db2 11 and well before migration to Db2 12, rebind any plans that job DSNTIJPM identifies.
Autobinds of plans can be particularly disruptive for application workloads that depend on few or even a single large plan.
In Db2 11 and well before migration to Db2 12, rebind any packages that job DSNTIJPM identifies, and specify the PLANMGMT(EXTENDED) bind option.
Regenerate any native SQL routines that were last regenerated in a release earlier than DB2 10.
When you issue a REBIND command for an application that uses native SQL routines, it rebinds only the SQL statements and not the control statements, so you must use ALTER PROCEDURE statements with the REGENERATE keyword. For more information, see Regenerating an existing version of a native SQL procedure.
Run the applications on Db2 11 for a period of time so that you have plenty of time to address any performance issues that might occur.
Free old package copies regularly, and especially just before the migration, to get recent original package copies.
The prior package is preserved for use by Db2 11. If a performance regression occurs, you can rebind the package with the SWITCH option to recover the previous access path.
Set the ABIND subsystem parameter to YES or COEXIST. (Db2 12 uses the same behavior for both settings.)
- In a data sharing group, if a package or plan is bound in Db2 12 and it runs later in Db2 11, it is automatically rebound. However, the Db2 11 plan or package can be used when it runs again in Db2 12 without another rebind,However, it runs without any Db2 12 enhancements in this case.
- In Db2 subsystems that are not Db2 data sharing members, releases coexistence is not an issue. If you must fall back to Db2 11, explicitly rebind packages and plans that were bound in Db2 12. By doing so, you can avoid remigration autobinds that occur for packages or plans that were bound in Db2 12 and are then automatically bound after fallback to Db2 11.
Bind most packages or plans in Db2 12 only after migration is complete for all members and coexistence with Db2 11 is no longer a concern.