Are you Packaged and Bound for Db2 13?

Written by: Julia Carter and Emil Kotrc, Broadcom

Package rebinds can sometimes be a real challenge for DBAs when it comes to Db2 upgrades. There are often a group which everyone knows by name and dreads the prospect of having to rebind them. Maybe they are part of one of the most core and frequently used transactions, or maybe they are related to internet banking login, or an application lookup which someone or something is calling 24x7, and in the past you have effectively needed an outage of the application in order to make that rebind. Maybe their access path is really sensitive and there have been issues in the past with an access path regression after a rebind so everyone is on heightened alert if that package ever needs to be rebound again. So, alongside the inevitable analysis that is required for checking the potential for access path changes, there is probably extra paperwork and several meetings to attend as well in order to get approval for these special packages to be rebound.

But when it comes to migrating to a new version of Db2, it’s inevitable that there will need to be some rebinds. This blog looks at the general requirements and recommendations for completing rebinds, and also a “perfect storm” scenario which you might want to check you aren’t sailing towards. 

There are some significant changes in Db2 13 because it cannot use the runtime structures from a package that was last bound in a release earlier than Db2 11 (similar to Db2 12 which couldn’t use runtime structures earlier than Db2 10). To avoid autobinds, it’s important that packages are bound at Db2 12, prior to migrating to Db2 13.

Another thing to note is that if a package is bound at Db2 13, it cannot run on Db2 12 members without an autobind back to Db2 12 which can potentially cause some disruption in a rolling online migration if the package is being executed on Db2 12 and Db2 13 members with an autobind being required each time it “swaps” between members at different versions (after an autobind back to v12, the package can then run on v13 without a subsequent rebind but without any Db2 13 enhancements).

This rebind requirement actually comes into play when you are trying to activate Db2 12 FL 510 as this enforces the completion of Db2 13 pre-migration activities and it will not activate successfully if any packages bound prior to Db2 11 are found (if the RELBOUND in the SYSIBM.SYSPACKAGE is different than ‘P’, ‘Q’, ‘R’, which is Db2 version 11 ,12, or 13) that have been used in the last 18 months (based on LASTUSED in the SYSIBM.SYSPACKAGE table).

We suggest that you initially verify the value that you have for DISABLE_EDMRTS. NO is the default and recommended option, and check that it is the same for all members of the group. This is important because the activation to M510 runs a query which uses the LASTUSED column in the SYSIBM.SYSPACKAGE table, and if the zparm is set to YES then this column may not be populated, meaning packages that have been recently used but not bound in or after Db2 11 will not show up as candidates for rebind.

It's also recommended that zparm ABIND is set to COEXIST for members of a datasharing group to allow autobinds of packages that are invalid when Db2 12 and 13 members are coexisting in a datasharing group.

It’s advisable to run DSNTIJPE/DSNTIJPM well in advance of migration to M510 to identify packages that might prevent you from activating the last function level in Db2 12, in particular report 08. In Db2 12, packages that hadn’t been bound in Db2 10 or later were also subject to an autobind the first time they were executed in Db2 12. So with that in mind, it’s possible that there may still be some packages that will be used in the future that haven’t had a rebind since Db2 10 that may get executed at some point after migration to Db2 13.

In this case, those packages would be subject to an autobind on first use at Db2 13. This might not be a big deal in some cases, the autobind happens, the access path remains the same (or even improves) and everything carries on as normal.

But what if it doesn’t?

One possible issue would be that the autobind actually gives an access path regression to a statement (or statements) in the package. In normal circumstances, if we are using Plan Management (Basic or Extended) then we could simply do another rebind and switch back to a previous access path, but that switch back isn’t possible when the old copy is not from version 11 or higher.

In the unlikely event (but of course, always possible) that you have a situation where you have packages that were last bound in Db2 10 or before and if you have the zparm DISABLE_EDMRTS=YES (so the packages could be in constant use but the LASTUSED column isn’t being updated due to the zparm) OR the packages haven’t been used for >18 months then they slip through the M510 checks and the Db2 13 checks and are therefore candidates for rebind on first use at Db2 13. But if the autobind fails – possibly due to contention as the autobind mechanism doesn’t use Rebind Phase-In functionality delivered in Db2 12 FL505 – then that package becomes inoperative.

If you are planning on coexistence of Db2 13 members with Db2 12 members, then rebinding packages at Db2 12 that might be subject to migration-related autobinds is the only way to ensure that good performance persists and that you mitigate the risks associated with autobinding packages in terms of contention and performance regression.

In addition to checking that the packages themselves are going to execute ok in Db2 13 without needing a migration-related autobind, it’s also important to review and free old package copies regularly, and particularly before a migration. The aim is to have recent, good copies of packages that are preserved for use at Db2 12, so if necessary, a package can be switched back to use the previous or original access path after a performance regression. 

Of course, we are lucky now to have more tools at our disposal for proactively analysing, managing and controlling access paths on rebinds and these should be exploited where possible: 

• Plan Management (preferably Extended in order to exploit rebind phase-in)
• APCOMPARE bind parameters
• APREUSE bind parameters
• Exciting new Rebind phase-in feature for packages that are being used for execution as delivered in FL505 and APAR PH12186

If you’re planning to go from Db2 12 FL500 to FL510 and then on to Db2 13, but if you expect that there are packages that will be subject to a migration-related autobind, you might want to consider activating FL509 as an extra step so that you can then plan rebinds (and take advantage of rebind phase-in) for those packages that would otherwise slip through the net and could cause disruptive autobinds at Db2 13.

Migrations always take some planning, but you could consider these steps to help to make the move to Db2 13 as smooth as possible:

1. Have all members of a datasharing group at the highest maintenance level (ideally all at M510).
2. Monitor for any incompatible changes (for a month or more if possible):
   • IFCID 239 has an indication of an incompatible change
   • IFCID 376 has the details
3. Prepare for the CATMAINT to M509 (the last Db2 12 catalog level), for example run -DIS BLOCKERS at the same time but a week before you plan to run CATMAINT to check for any potential blockers and check the catalog for dependent packages which might be affected.
4. Run CATMAINT to advance the catalog level to M509.
5. Leave it for some time (several weeks to months)
6. Run DSNTIJPE premigration job (DSNTIJPE from Db2 12 SDSNSAMP, or DSNTIJPM from Db2 13 SDSNSAMP), and also -ACTIVATE FUNCTION LEVEL (V12R1M510) TEST, this should not take issue with the catalog level (M509 at this point from step 3) but may fail due to packages that are identified for needing a rebind that have not yet been completed. Review message DSNU757I for more details.
7. Depending on the -ACTIVATE FUNCTION LEVEL (V12R1M510) TEST result:
   1. If M510 cannot be activated and the packages cannot easily be rebound, then plan to activate function level M509 instead and verify it will work: -ACTIVATE FUNCTION LEVEL (V12R1M509) TEST.
   2. If there are no packages to rebind, or they have been successfully rebound at Db2 12 followed by a successful activation test, then M510 can be activated.
8. If step 7.1 was followed then plan to rebind the packages identified in DSNTIJPE, taking advantage of rebind phase-in (with PLANMGMT(EXTENDED)). DSNTIJPE also generates the REBIND commands.
9. Run ACTIVATE FUNCTION LEVEL (V12R1M510) which should now complete successfully.
10. Get the fallback SPE which is a pre-requisite for migrating to Db2 13: PH37108
11. FREE the PHASEOUT packages

• • New subcommand: PLANMGMTSCOPE(PHASEOUT)
  • IFCID 393 to identify copies that cannot be free

12. FREE the inactive package copies using PLANMGMTSCOPE(INACTIVE). Again, DSNTIJPE can help to identify those copies.

By taking those steps to successfully activate Db2 12 FL510, the subsequent migration to Db2 13 should be a cinch (from a package perspective anyway!).

But of course, once all members are at Db2 13 and there is no likelihood of falling back to Db2 12, you then need to plan to rebind everything again at Db2 13!