Db2 for z/OS and its ecosystem

This Db2 News from the Lab blog entry was originally published on 2019-10-18.

By Paul McWilliams and Tammie Dang.

Db2 12 function level 505 (activation enabled by APAR PH09191) introduces a new REBIND phase-in capability for application packages, which enables DBAs to issue REBIND PACKAGE commands successfully when target packages are being concurrently executed. This new capability enables DBAs to deploy the new features in new Db2 12 function levels, which often require packages to be rebound at a higher APPLCOMPAT level, without having to either wait for a suitable window when applications are not running, or take a disruptive application outages to complete the rebinds. Before function level 505, Db2 requires that all threads that are executing a package to quiesce before a rebind can succeed. That requirement often makes it nearly impossible for DBAs to find a time to run rebinds when many applications that require 24x7 availability.

Another advantage of the new REBIND phase-in capability is for switching to a previous package copy while transactions are running by phasing-in the switch to the previous access path, which allows for regression recovery more promptly.

The new REBIND phase-in capability builds on the existing plan management and package copies concepts in Db2, so that the REBIND PACKAGE command generates a new package copy while existing threads continue to execute using a package copy that is being phased out. When the REBIND PACKAGE command commits, the new copy becomes the current copy and it is immediately available for execution by any new threads. Threads that existed before the REBIND command can also use the new copy after they release the phased-out copy, based on either the RELEASE(COMMIT) or RELEASE(DEALLOCATE) options.

The REBIND phase-in capability is supported when the REBIND package command specifies the PLANMGMT(EXTENDED) option. If the APREUSE(WARN) or AREUSE(ERROR) option is specified, the APREUSESOURCE(CURRENT) option must also be specified. The REBIND phase-in capability is not supported if the package is for a trigger, SQL stored procedure, or user-defined function.

Before this new capability, there are at most only three copies for a given package: current, previous, and original. The current copy always has copy ID 0 and is shown in the SYSPACKAGE catalog table. The previous and original copies have copy ID 1 and 2 and can be found in SYSPACKCOPY.

With the introduction of the REBIND phase-in capability, a REBIND PACKAGE command might create a new current copy for the package while retaining the existing current copy as a phase-out copy. There can now be multiple copies for a given package: there is still only one current copy, one original copy, and one previous copy, but there can now be multiple phased-out copies.

Db2 can generate as many as 14 copies of a single package as a result of the REBIND phase-in, COPYID=16 is currently the maximum copy ID value that Db2 uses, and COPYID=3 is reserved for internal use. One copy is always the current copy and Db2 stores its copy ID in a SYSPACKAGE catalog table row. Db2 stores all phased-out copies in the SYSPACKCOPY catalog table, and other catalog tables, until they are deleted. On subsequent REBIND PACKAGE command, Db2 detects whether to delete phased-out copies and reuses the copy IDs. If all 14 copy ID values are already in use, the next REBIND command fails and Db2 issues message DSNT500I with reason code 00E30307.

Examples

Let's look at some examples. Note that the copy ID values used here illustrate how Db2 phases-in the package copies, and Db2 is not guaranteed to select the copy ID values as shown here in every scenario.

Assume that the package NULLID.SYSLH100 was previously bound with the APPLCOMPAT(V12R1M500) option. The following table illustrates the SYSPACKAGE catalog table row for the package:

SYSPACKAGE rows for the example NULLID.SYSLH100 package

Example 1: rebind with phase-in

Assume that you issue a REBIND PACKAGE command and specify the PLANMGMT(EXTENDED) and APPLCOMPAT(V12R1M501) options. If existing threads are using the package for execution, the following table illustrates the resulting update in the SYSPACKAGE catalog table. All new threads begin using the phased-in packaged copy, which is represented by the row with COPYID=4. (Notice that Db2 uses COPYID=3 for internal use only, and does not store it in the SYSPACKCOPY or SYSPACKAGE catalog tables.)

Updated SYSPACKAGE rows for example 1

The following table illustrates the resulting updates in SYSPACKCOPY catalog table rows for the same REBIND PACKAGE command. Threads that existed before the REBIND continue to execute using the phased-out package copy represented by the row with COPYID=0 row as long as necessary.

The copies with COPYID 1 and 2 were generated as usual, for the existing plan management support with PLANMGMT(EXTENDED), and their contents are the same as copy 0 in this case.

Updated SYSPACKCOPY rows for example 1

Eventually, a future REBIND PACKAGE command for the same package deletes the COPYID=0 row, if it is not being used. Also, if Db2 detects that a package is not being used for execution when you issue the original REBIND PACKAGE, it skips the phase-in process and immediately replaces the current copy ID. Any REBIND PACKAGE command can delete unused phased-out copies, regardless of whether it uses the phase-in process. FREE PACKAGE and BIND PACKAGE commands can also delete unused phased-out copies.

Example 2: Rebind with SWITCH(ORIGINAL)

Db2 also supports the new phase-in capability when the source for the rebind is either the previous or original package copy. When threads are executing the current copy of a package, a REBIND PACKAGE command with REBIND SWITCH generates a new current copy of the package from the specified source (previous or original) and, assuming that the copy is marked VALID, makes it available for the next execution. The phased-out copy also becomes the the previous copy.

Continuing from the previous example, and assuming that the COPYID=0 row was deleted by a subsequent REBIND, assume that you issue a REBIND PACKAGE command that specifies the REBIND SWITCH(ORIGINAL) option. Db2 uses COPYID=0 again because it is now available. The following tables illustrates the resulting update in the SYSPACKAGE and SYSPACKCOPY catalog table.

Updated SYSPACKAGE rows for example 2

COLLID|NAME|COPYID|APPLCOMPAT|Copy type NULLID|SYSLH100|0|V12R1M501|Current

Updated SYSPACKCOPY rows for example 2

Example 3: another rebind with existing phased-out copy

Again continuing from the previous example assume that existing threads are executing the packages copies with COPYID=4 and COPYID=0, and you issue a REBIND PACKAGE command that specifies the APPLCOMPAT(V12R1M502) command. A new copy with COPYID=5 in SYSPACKAGE is created, as illustrated by the following tables.

Updated SYSPACKAGE rows for example 3

Updated SYSPACKCOPY rows for example 3

Because more package copies are stored for phased-out copies, some additional storage is used. However, to avoid incurring the increased storage for the extra package copies, you can quiesce threads which execute the phased-out copies and rebind the package. You can find the package copy that a thread executes, which is either the current copy or the phase-out copy, in the package accounting trace record.

Db2 deletes unused phased-out copies on subsequent REBIND PACKAGE commands. The FREE PACKAGE and BIND PACKAGE commands also delete phased-out copies.

Tammie Dang is a Senior Software Engineer in Db2 for z/OS development and Paul McWilliams is an Information Developer for Db2 for z/OS.