DB2 11 for z/OS Bind Concurrency - Even Better Than You Think*

For the asterisk at the end of the this blog entry's title, you could substitute, "If what you think is based on what you've likely read or heard about this feature up till now." In the space below, I'll provide some background information on DB2 11's bind concurrency enhancement, and then I'll give you the bonus insight that you might see as the "cherry on top" -- the important scenario that is addressed by DB2 11 bind concurrency but which has received little (if any) coverage in documents and presentations that I've seen to date.

OK, background: first, the term "bind concurrency" is kind of a nickname for the DB2 11 feature of interest here. A longer and more comprehensively descriptive name for the enhancement could be worded as follows: "the capability through which BIND, REBIND, DDL, and utility operations can execute successfully in a DB2 11 new-function mode environment when they would otherwise have been blocked by RELEASE(DEALLOCATE) packages executed via persistent threads (or blocked by another circumstance)." That's a mouthful. You can see why people use the short descriptor, "bind concurrency." That "other circumstance" in the italicized parenthetical phrase has to do with the "cherry on top" aspect of bind concurrency to which I referred previously, and which I'll describe momentarily.

About RELEASE(DEALLOCATE) and persistent threads: this is a combination that has long been utilized at DB2 sites to improve the CPU efficiency of DB2-accessing applications. It's something about which I've blogged a number of times over the years, most recently in an entry posted here just a couple of weeks ago. A persistent thread (i.e., one that persists through commits) can stick around for quite some time, and therein lies a rub that can be encountered when a package bound with RELEASE(DEALLOCATE) is executed by way of such a thread: the package is considered to be in-use as long as the thread exists, because it won't be "released" until thread deallocation time (versus being released at each commit). This potentially lengthy and continuous (for the life of the thread) "use" of the package (not to be confused with "execution" of the package -- a package, once executed through a persistent thread, retains "in use" status until thread deallocation, even if the thread won't be deallocated for a while after package execution has completed) can interfere with a variety of database administration operations. For example, a package cannot be rebound while it is in use. A DDL operation that would cause a dependent package to become invalidated, such as adding a partition to a range-partitioned table, will not succeed while the package is in use, because a package can't be invalidated while it's in use. Similarly, a utility operation that would cause a package to be invalidated, such as an online REORG executed for the purpose of materializing a pending DDL action, will not succeed if the package is in use.

So, RELEASE(DEALLOCATE) combined with persistent threads is a CPU efficiency booster, but from a DB2 DBA's perspective it can gum up the system in some respects. This flip side of RELEASE(DEALLOCATE) plus persistent threads was addressed nicely for high-performance DBATs (a type of persistent thread introduced with DB2 10 for z/OS) through the introduction of the PKGREL keyword of the -MODIFY DDF command: when you need a "window" in which to perform bind/rebind, DDL, or utility operations that might be blocked by high-performance DBATs, you issue the command -MODIFY DDF PKGREL(COMMIT) and that effectively turns off high-performance DBATs until further notice (that "further notice" is indicated via issuance of the command -MODIFY DDF PKGREL(BNDOPT), which re-enables high-performance DBAT functionality).

That's nice for high-performance DBATs, but what if RELEASE(DEALLOCATE) packages are used with other persistent thread types, such as CICS-DB2 protected entry threads or long-running batch jobs (a batch job's thread doesn't go away until the job completes, regardless of the number of commits issued in the course of the job's execution)? That's where DB2 11 bind concurrency comes in. DB2 11 for z/OS introduces a new ZPARM parameter, PKGREL_COMMIT, that can be set to YES (the default) or NO. In a DB2 11 new-function mode environment, PKGREL_COMMIT YES has this effect: when a BIND/REBIND, DDL, or utility operation would be blocked because of a RELEASE(DEALLOCATE) package tied to a persistent thread, a flag is set for that package and any persistent thread to which the package is allocated will automatically switch to RELEASE(COMMIT) behavior for the package at the next commit (or rollback). Voila - the package is released when associated persistent threads commit, and the BIND/REBIND, DDL, or utility operation that would otherwise have hit a package-related concurrency wall can instead execute to completion. Pretty slick, eh?

There are a few caveats I should mention: PKGREL_COMMIT YES does not free up a RELEASE(DEALLOCATE) package at the next commit point for a persistent thread when 1) the package has a WITH HOLD cursor that is open at the time of the next commit, or 2) the package was bound with KEEPDYNAMIC(YES), or 3) the next commit (or rollback) is issued by a stored procedure.

Now for the promised cherry on top: in looking at documentation and/or presentations that describe DB2 11 bind concurrency, you may find yourself thinking:

"Nice feature, but it appears to be applicable only to situations involving RELEASE(DEALLOCATE) packages and persistent threads. In my DB2 system, we've run into package-related bind, DDL, and utility concurrency problems that have nothing to do with RELEASE(DEALLOCATE) and persistent threads. These cases involve packages executed by high-volume transactions. We have trouble sometimes getting bind, DDL, and utility operations to go through to successful completion. These operations get blocked by an in-use package. Even though the package is bound with RELEASE(COMMIT) and is not executed by way of persistent threads, it is in-use for long stretches of time because there is always at least one transaction using it at any given time. In other words, executions of some of our high-volume transactions overlap so that the use count for associated packages rarely goes to zero. When are we going to get some relief from that problem?"

Guess what? You have gotten relief from that problem, and the relief is in the form of the DB2 11 bind concurrency feature about which I've been writing in this blog entry. See, even though the write-up -- in documents and presentations -- about DB2 11's bind concurrency feature always (as far as I've seen) mentions RELEASE(DEALLOCATE) and persistent threads, that's NOT the only scenario to which bind concurrency is applicable. It also addresses bind, DDL and utility blockage situations caused by in-use RELEASE(COMMIT) packages that stay in-use for long stretches of time because of overlapping executions of related transactions. Bind concurrency functionality does this by providing something akin to a drain locking mechanism for packages (drain locking, introduced way back in the nineties, pertains to table spaces and indexes and partitions of same, and is used by DB2 utilities and also some commands and SQL statements). When PKGREL_COMMIT YES is in effect and an in-use package needs to go out-of-use to allow a bind or DDL or utility operation to complete, the package in question is flagged by DB2 and any transaction that would use the package will hold off on using the package as long as the flag remains in place; thus, as in-flight transactions associated with a flagged package hit commit points and other transactions hold off on using the package, the package's use count goes to zero and the bind or DDL or utility operation that is waiting to proceed gets the green light and gets done. After that, normal application processing resumes.

And there you have it: DB2 11 bind concurrency is even better than advertised. If you're not running DB2 11 yet, this gives you one more reason to get going with migration planning.