This Db2 for z/OS News from the Lab blog entry was originally published on 2017-09-28.
By Patrick Bossman and Paul McWilliams.
Automatic binds (sometimes called AUTOBIND) of plans and packages for applications that run on Db2 for z/OS introduce avoidable risk to online and offline migrations. During and after migration, automatic binds can cause costly problems, including performance regression and even application outages.
However, you can take action before migration to avoid such automatic binds and costly outages that might result. We recently added information about Avoiding automatic binds at migration, to help you understand when Db2 uses automatic binds during migration, and the actions that you can take to avoid their disruptive effects.
Performance regressions and even application outages can result from migration-related automatic binds when the following problems occur:
- Application failures, when automatic binds fail because of contention between different releases in coexistence because an up-level member needs to automatically bind a plan or package (which DSNTIJPM identified as a candidate for rebind before migration) that is in use by a down-level member.
- Application failures, when automatic binds fail for access control authorization exit users because the authorization ID for the automatic bind has insufficient privileges, compared to the owner of the package or the plan.
- Performance regressions that are difficult resolve because the automatic bind destroys the prior packages, eliminating REBIND with SWITCH or APREUSE as a viable query performance recovery options.
- The "autobind ping-pong" of repeating automatic re-migration binds, each time that the other release in coexistence runs the application again. Update January 2018: To eliminate re-migration binds entirely, apply the PTF for APAR PI87675. It eliminates repeating re-migration binds completely by making ABIND=YES behave like ABIND=COEXIST.
After you migrate to Db2 12, any plan or package that was last bound or rebound in a release earlier than Db2 10 is automatically bound. The same is true at migration to Db2 11 for plans and packages last bound or rebound in releases earlier than Db2 9. These automatic binds occur because the new Db2 release cannot use the run-time structures from plans or packages that were last bound in the earlier release.
Such problems can occur any time during the "first impression window" for the new Db2 release. That is, they can occur any time after migration, until the plan or package first runs in the new release, and as long as release coexistence continues. For more information, see Automatic binds in coexistence.
Actions to take
To avoid automatic binds and substantially reduce migration risk, take the following actions well before (months if possible) migration to the new release, so that you have sufficient time to address any performance issues that might occur after the rebinds.
- Run the premigration queries (job DSNTIJPM) to identify any plans and packages that are subject to automatic binds in the new release.
- Rebind any plans that DSNTIJPM identifies. Automatic binds of plans can be particularly disruptive for application workloads that depend on very few or even a single large plan.
- Free inactive package copies, then rebind any packages that DSNTIJPM identifies with the PLANMGMT(EXTENDED) option. Do this well before (months if possible) migration to the new release. Then run the applications on the old release for a period of time so that you have sufficient time to address any performance issues that might occur after the rebind. (Performing FREE INACTIVE then REBIND moves the current package copy, which is presumably performing well, to the original slot, making it available to be restored with REBIND SWITCH, if a performance regression occurs after REBIND.)
- Set ABIND=COEXIST in data sharing. This setting prevents the "autobind ping-pong" of automatic re-migration binds. Update January 2018: Apply the PTF for APAR PI87675. It eliminates repeating re-migration binds completely by making ABIND=YES behave like ABIND=COEXIST.
- In non-data sharing Db2 subsystems, set ABIND=YES because coexistence is not an issue.
- When fallback occurs in non-data sharing Db2 subsystems, explicitly rebind any package that was bound on the up-level release. Then rebind any packages or plans that were automatically bound after fallback. Without explicit rebinds, automatic bind occurs for those packages and plans at re-migration to the up-level release.
- Bind or rebind most packages and plans on the new release only after the migration is complete for all members, and automatic rebinds on down-level members in release coexistence are no longer a concern.
Related information
#Db2forz/OS#Db2Znews