After Migrating to a New Version of Db2 for z/OS, Rebind All Your Plans and Packages

At this writing, a lot of Db2 for z/OS-using organizations are actively engaged in migrating to Db2 13 from Db2 12, and plenty of sites have recently gotten to Db2 13. That being the case, it seems like a good time to remind people of an important step to take after migrating to Db2 13, while still at the V13R1M100 function level (the initial Db2 13 function level when you've migrated from Db2 12): rebind all your plans and packages.

First: why do this? For a couple of reasons. One would be to get a CPU efficiency benefit. The other has to do with the benefit of keeping package code current (I'll explain that "code" reference momentarily). Let me expand on both these rebind motivations.

Package REBIND motivation #1: improved CPU efficiency

Rebinding packages when you get to a new version of Db2 for z/OS can deliver CPU efficiency benefits in two ways. First (for packages associated with programs that issue static SQL statements), it lets the Db2 optimizer generate access paths for queries (and for "searched" UPDATEs and DELETEs - i.e., UPDATE and DELETE statements that have predicates) using options that may not have been available to the optimizer in the previous version of Db2. In some cases, new access path options can substantially improve a query's performance.

What if you're happy with the current access plan for a package (very often the case)? That brings us to the second source of CPU efficiency gain typically associated with a package rebind in a new-version Db2 environment: even when access paths don't change as a result of a REBIND (and they generally won't when you issue REBIND PACKAGE with the option APREUSE(WARN) or APREUSE(ERROR), which tells Db2 to re-use existing access paths for the package's SQL statements), it's quite likely that the rebind of a package in a new-version Db2 environment will yield at least some improvement in the CPU efficiency of package execution. How is that so? Well, as briefly mentioned above, a Db2 package contains executable code. For a package related to a program that utilizes static SQL, part of the package's executable code will be, in essence, the compiled form of the static SQL statements. See, there is no z/OS or IBM Z instruction called (for example) SELECT. A SELECT statement (or any other SQL statement) embedded in a Db2-accessing program has to be turned into code that can be executed in the z/OS system. That executable-form SQL is found in the program's Db2 package (itself originally generated from a BIND of the program's static SQL statements). And here's the thing: the IBM Db2 for z/OS development team is always working to "tighten up" executable-form SQL (i.e., to make the executable form of SQL statements ever more CPU-efficient). When you let Db2 13 re-generate the code in a package by rebinding that package, you typically will get some CPU efficiency benefit from the resultant "tightened up" code that Db2 13 can deliver - again, even when access paths for the package's SQL statements don't change.

Package REBIND motivation #2: package code currency

Efficiency benefits of tightened-up package code aside, there is another reason to let a new version of Db2 for z/OS regenerate the code for your packages via REBIND: it makes the package code more current in the sense that it is associated with the newer version of Db2. Why is that important? Because at some point too-old package code (i.e., code generated for a package via BIND or REBIND with an older version of Db2) will become un-executable for a newer version of Db2. That was the case in a Db2 12 for z/OS environment for packages last bound or rebound in a pre-Db2 10 system, and it is true in a Db2 13 environment for packages last bound or rebound in a pre-Db2 11 system (that is why activation of Db2 12 function level 510 - a prerequisite for migration to Db2 13 - will not be successful if you have any pre-Db2 11 packages still in use in your Db2 12 environment). If you keep your Db2 package code current by rebinding packages when you go to a new version of Db2, this issue of eventually-not-executable package code will be something about which you won't have to worry.

Good Db2 13 news regarding REBIND PACKAGE with APREUSE(WARN) or APREUSE(ERROR)

As I mentioned previously, it's often the case that a Db2 DBA team is fine with package access paths being what they are. In such situations, when doing a large-scale rebind of packages upon migrating to a new version of Db2, it makes sense to specify APREUSE(WARN) or APREUSE(ERROR) in the REBIND PACKAGE commands. Both forms of APREUSE tell Db2 to reuse existing access paths for a package's SQL statements, versus generating new access paths. The difference? With APREUSE(WARN) in effect, if Db2 cannot re-use an access path for one or more of a package's SQL statements, the rebind will go forward and Db2 will issue an information message about the access path change and will write information to EXPLAIN tables that will allow you to see just what changed. With APREUSE(ERROR) in effect, a REBIND PACKAGE action will fail if Db2 cannot reuse all access paths associated with a package's SQL statements.

Here's the good Db2 13 news regarding APREUSE(WARN) and APREUSE(ERROR):

• The CPU efficiency of REBIND PACKAGE when APREUSE(WARN/ERROR) is specified has been improved, so a large-scale rebind with these specifications will consume less CPU time.
• The rate of warnings, when APREUSE(WARN) is used, or errors, when APREUSE(ERROR) is used, should be lower in a Db2 13 system versus previous Db2 environments. In other words, the rate of success for access path reuse in a Db2 13 environment should be higher versus previous Db2 environments.

A word about APPLCOMPAT

When you do a large-scale rebind of packages after migrating to Db2 13, do you need to change the APPLCOMPAT value for your packages? No, you do not need to do this. As is the case for a Db2 12 system, in a Db2 13 environment APPLCOMPAT values as low as V10R1 are OK. Additionally, you should know that the CPU benefit of package rebind that I described previously does NOT require going to a higher APPLCOMPAT value when rebinding packages; and, if you want to let the Db2 13 optimizer utilize new access path options, by not using APREUSE(WARN) or APREUSE(ERROR) in a REBIND PACKAGE command, this does NOT require specification of a higher APPLCOMPAT value for the package in question - query optimization is unrelated to a package's APPLCOMPAT value.

If you'd prefer to take package APPLCOMPAT values higher when rebinding packages in a Db2 13 environment, go ahead and do that - just know that this is an option, and not a technical requirement.

A little more information here:

• When a package is rebound and the REBIND PACKAGE command does not include an APPLCOMPAT specification, by default Db2 will just preserve the package's existing APPLCOMPAT value.
• You can learn more about APPLCOMPAT via a part 1 and part 2 entry I posted to this blog a few years ago.

Remember that rebind phase-in will help you out

Rebind phase-in functionality, introduced with Db2 12 function level 505 and part of the Db2 13 base code, can be a BIG help when you're looking to rebind all of your packages following migration to Db2 13. How so? By eliminating REBIND contention with in-use packages. Prior to Db2 12 FL505, you couldn't rebind a package if the package was in-use at the time (i.e., if the package was allocated to a thread for execution), and this caused many REBIND PACKAGE commands to time out (especially if the target package was bound with RELEASE(DEALLOCATE) and allocated to one or more threads of the type that persist through COMMITs). With rebind phase-in functionality in effect (and this is automatic in a Db2 12 FL505 or higher environment, or in a Db2 13 system, with no special REBIND specification required), a REBIND package action will succeed even if the specified package is in-use at the time, and the rebind itself will not disrupt a related application workload.

Rebind phase-in functionality can be particularly helpful for rebinding packages that are very high-use in nature, as is often the case for (among others) the IBM Data Server Driver / Db2 Connect packages (referring to the packages whose default collection is the one named NULLID). At sites with high-volume, round-the-clock Db2 DDF (i.e., client-server) workloads, rebinding the NULLID packages could be a real challenge. Rebind phase-in removed that challenge.

NOTE: rebind phase-in functionality requires that the value of the Db2 ZPARM parameter PLANMGMT be set to EXTENDED. As that has long been the default value for PLANMGMT, it's probably the setting for your Db2 for z/OS systems, but you might want to double-check that.

Rebind plans, too - and note some distinctions versus packages

Yes, plans - as well as packages - should be rebound following a migration to Db2 13, but there are some difference versus packages that you should consider.

First, plan rebinds apply exclusively to your "local-to-Db2" applications, such as CICS-Db2 or IMS-Db2 transactions, or Db2-accessing batch jobs - DDF-using applications are not associated with Db2 plans in a technical sense (the DISTSERV plan related to DDF-using applications is for reporting and instrumentation purposes - it's not a Db2 plan in the technical sense).

Second, rebinding of plans when you get to Db2 13, while recommended, is not as high-priority an action as is rebinding of packages. Plans don't contain code that provides the executable form of SQL statements - it has been a LONG time since you were able to bind SQL statements directly into plans, and existing plans containing compiled SQL statement code had to be converted to package-related plans years ago (a Db2 plan points to one or more collections of packages that are executed by applications that utilize the plan). This being the case, you'll be more in take-your-time mode when it comes to rebinding plans in the Db2 13 environment, and that relatively diminished level of urgency is helpful in light of the fact that there is currently no plan-related equivalent of the phase-in functionality used for package rebinds.

Given that there can be contention when a REBIND PLAN command targets an in-use plan, timing can be important. For rebind of a plan used by a batch application, it generally won't be hard to find a time window during which the batch application is not running. Even if you have a round-the-clock transaction (CICS or IMS TM) that accesses Db2 for z/OS, you should be able to successfully execute a REBIND PLAN if you issue the command during a time of lower transaction volume and if the plan in question is not bound with the RELEASE(DEALLOCATE) option (in my experience, RELEASE(DEALLOCATE) is typically used for certain packages, and not for plans). If RELEASE(DEALLOCATE) is in effect for a plan you want to rebind, and that plan is associated with a round-the-clock transaction workload, and the plan is allocated to persistent threads (e.g., CICS protected threads or IMS WFI or pseudo-WFI threads), you might need to look at briefly going with non-persistent threads for the workload to enable successful execution of REBIND PLAN.

Summing it up

Rebinding your Db2 plans and packages after going to Db2 13 (while still at the V13R1M100 function level) should be part of your overall Db2 13 migration plan. Taking this action should yield some CPU efficiency gains (even when access paths for static SQL packages don't change), and the boost in package and plan code currency will help to facilitate migration to the version of Db2 that follows V13 (whenever "Vnext" makes the scene).