DB2 10 for z/OS: What do You Know About High-Performance DBATs?

DB2 10 for z/OS, which became generally available this past October, is loaded with features and functions that can reduce the CPU cost of applications. One of my favorites is high-performance DBATs (DBATs, or database access threads, are associated with SQL statements that are issued by remote requesters using the DRDA protocol and processed via DB2's Distributed Data Facility). I'm big on high-performance DBATs because I'm big on mainframe DB2's use as a super-scalable, super-available, super-secure data server for applications that run on Java, .NET, and other application servers. For DB2 for z/OS to realize its full value-delivery potential in client-server computing environments, it has to provide to DBAs and to systems administrators the same performance management and tuning options that are available for local-to-DB2 applications (such as those that run in CICS regions). This has been happening for a period that stretches back a good 20 years or so. First we got static SQL for client-server applications (back when the DRDA protocol and package bind were introduced with DB2 Version 2 Release 3). With DB2 Version 4 we got stored procedures, a critical enhancement with respect to driving up the scalability and manageability of DB2-based client-server computing (stored procedures themselves have been substantially enhanced since their introduction, with features such as WLM-managed stored procedure address spaces and, more recently, native SQL procedures). Management of DB2 DDF transaction priority through WLM service classes provided a means of setting performance objectives and report classes that could be very granular, with transaction classification possible by way of all kinds of attributes including collection, package, stored procedure, client application name, and user ID.

All this is great, and plenty of organizations are running very high-volume OLTP workloads in mainframe DB2 client-server environments today. That said, there was one thing annoyingly missing from the DDF transaction-tuning tool kit: the ability to leverage the CPU efficiency benefits of packages bound with RELEASE(DEALLOCATE), used in combination with threads that persist through multiple transaction executions. That gap was filled very nicely by the introduction, with DB2 10 for z/OS, of high-performance DBATs.

Before getting into the particulars of high-performance DBATs, I want to do a little illustration by comparison. High-performance DBATs are conceptually similar to CICS-DB2 protected entry threads (with some important differences, which I'll cover momentarily). Like CICS-DB2 protected entry threads, high-performance DBATs persist through multiple transaction executions (typically, a non-protected CICS-DB2 thread will terminate when the transaction using that thread completes). This persistence, in and of itself, is not such a big deal in the DB2 DDF world. For one thing, thread creation and termination doesn't consume a whole lot of resource (the DB2 systems services address space, aka MSTR, handles this function, and you can see in a DB2 monitor statistics report that MSTR consumes relatively little CPU). More importantly, if you have the DSNZPARM parameter CMTSTAT set to INACTIVE (the default), DB2 DDF connections are disassociated from DB2 DDF threads, and the latter can be pooled and used to support a very large number of connections, so that DBAT re-use will typically be very high and the incidence of DBAT creation will typically be very low.

So, if the re-usability of high-performance DBATs is not the big news, what is? The big news is that high-performance DBATs allow you to realize the CPU efficiency benefit of the RELEASE(DEALLOCATE) option of BIND PACKAGE -- a benefit previously available only for packages associated with local-to-DB2 programs. Back to the CICS-DB2 protected entry thread analogy. The real bang for the buck there is the use of such threads for the execution of packages bound with RELEASE(DEALLOCATE). Why? Because, for a package so bound, certain resources, such as table space locks and EDM pool elements, will be retained until the thread used in the execution of the package is deallocated -- this as opposed to being released and then reacquired, over and over again, as a high-volume CICS-DB2 transaction is executed (and retention of table space locks is generally not a big deal, as these are usually intent locks, and intent locks do not conflict with each other). Think about it: suppose a CICS-DB2 transaction accesses data in four table spaces, and the package has several sections that are stored in the EDM pool (the place where packages -- the run-time structures created from binding programs with embedded static SQL -- are cached in memory). If the transaction program's package is bound with RELEASE(COMMIT), when the transaction executes it will get locks (again, almost certainly intent -- versus exclusive -- locks) on the four table spaces and space in the EDM pool for the required package sections, and when it completes (driving a DB2 commit) those tablespace locks and EDM pool elements will be released. If the same transaction is executed right after that first one, those same tablespace locks and EDM pool elements will be reacquired and then released again at transaction completion. All that acquisition and release of the same stuff adds to the CPU cost of the transaction.

If the CICS-DB2 transaction program's package is bound with RELEASE(DEALLOCATE), and IF the thread used to execute the package can persist through executions of the program (that's where CICS-DB2 protected entry threads come in), the tablespace locks and EDM pool elements acquired for the program will be retained until the thread is deallocated, and that will likely not happen until the transaction has been executed a LOT of times; so, RELEASE(DEALLOCATE) plus protected entry threads together deliver a nice CPU efficiency benefit for high-volume CICS-DB2 transactions.

What kept that benefit from being available for transactions that execute in a client-server DB2 environment? It wasn't thread persistence -- as mentioned, DBATs, once created, can be reused many, many times. The problem was the RELEASE(DEALLOCATE) part of the equation. See, for a long time (since DB2 V6, I believe), although you could bind a package executed by remote DRDA requesters with RELEASE(DEALLOCATE), DB2 would not honor that bind specification in a DDF setting -- if an execution of the package were associated with a DBAT (versus a local-to-DB2 program's thread), DB2 would handle the package as though it had been bound with RELEASE(COMMIT). Why? Because, as noted, DBATs can stick around for a long time, and having tablespace locks -- even intent locks -- held by such long-duration threads could interfere with certain database maintenance operations (utilities, in particular). Now that high-performance DBATs have made the scene, RELEASE(DEALLOCATE) can be honored by DB2 even for packages executed on behalf of remote requesters via DBATs.

I mentioned that there are some important differences between CICS-DB2 protected entry threads and high-performance DBATs. One of those key differences concerns the actions required to bring the two different kinds of threads into existence. To have CICS-DB2 protected entry threads, one has to use CICS resource definition online functionality (aka RDO) to define a DB2ENTRY resource with a PROTECTNUM (and a THREADLIMIT) value that is greater than zero. A high-performance DBAT, on the other hand, is instantiated when it is used in the execution of a package bound with RELEASE(DEALLOCATE) -- there is no set-up, per se, required in order to have high-performance DBATs in your DB2 system (aside from the need to set the MAXDBAT and CONDBAT DSNZPARM parameters to values that are greater than zero, and to have CMTSTAT in DSNZPARM set to INACTIVE -- but that's what you've probably done anyway if you do any DDF processing on your system). Let's say that a remote requester issues an SQL statement that causes a DB2 package bound with RELEASE(DEALLOCATE) to be executed. Here's what will happen:

• If there is already a high-performance DBAT in existence and associated with this particular connection (more on that in a moment), and it's available, it will be used in the execution of the package.
• If there is not already a high-performance DBAT in existence for this connection, or if there is and it is tied up in the execution of another package, a "regular" DBAT in the pool will become a high-performance DBAT and will be used to execute the package (or a new DBAT will be created and will be a high-performance DBAT, if there are no available "regular" pooled DBATs).

Once the high-performance DBAT has been instantiated, it will be used to process 200 units of work and will then be terminated (if it is not terminated before as a result of hitting the idle thread timeout threshold). Auto-termination after 200 units of work is done to periodically release resources held by the high-performance DBAT. Even with that periodic break in the action, there will be times when you want resources acquired in the execution of packages to be released at commit, no matter how the packages are bound (perhaps during a period of time during which you want to run a number of utilities involving tablespaces targeted by SQL statements in the client-server packages). That can be easily accomplished thanks to a new command, -MODIFY DDF, that was introduced with DB2 10. If you issue -MODIFY DDF PKGREL(COMMIT), packages executed via DBATs will release resources at commit, no matter what RELEASE option was specified when the package was created (again, we're talking about tablespace locks and EDM pool elements -- retention of page and row locks is not affected by the RELEASE option of BIND PACKAGE). When this period of utility (for example processing is done, you can issue -MODIFY DDF PKGREL(BNDOPT) to have DB2 once again honor the RELEASE(DEALLOCATE) specification for packages so bound that are executed via DBATs.

Now, I implied previously that a high-performance DBAT, once instantiated, will be dedicated to the connection through which the request invoking the RELEASE(DEALLOCATE) package was issued. This is in fact the case. A high-performance DBAT does not go into the pool when it is freed up, to be usable by other connections to the DB2 subsystem. Instead, if the RELEASE(DEALLOCATE) package invocation came from a connection established by application server XYZ, the high-performance DBAT instantiated as a result of that request will persist to process 199 more units of work from application server XYZ; furthermore, there is no requirement that all -- or any -- of those 199 additional units of work be associated with packages bound with RELEASE(DEALLOCATE) -- a high-performance DBAT can be used for the execution of packages bound with RELEASE(COMMIT), too.

If you're using DB2 10 now, or you're planning your migration to this new DB2 release, I encourage you to think about taking advantage of high-performance DBATs to improve the CPU efficiency of your DDF workload. In other words, I want you to think about binding packages executed frequently via DBATs with RELEASE(DEALLOCATE); and, don't limit your consideration to packages associated with frequently executed programs that issue static SQL statements (though such packages, which could be related to DB2 stored procedures, can be excellent candidates for binding with RELEASE(DEALLOCATE)). You should also consider binding with RELEASE(DEALLOCATE) packages used by remote programs that issue dynamic SQL statements. Here, I have particularly in mind the packages used by the IBM Data Server Driver for JDBC and SQLJ. You can read more about binding these packages with RELEASE(DEALLOCATE) in the IBM "red book" titled "DB2 10 for z/OS Technical Overview" (see section 9.5).

The high-performance DBAT feature of DB2 10 is one of the latest in a long line of enhancements that have made DB2 for z/OS more and more valuable as a foundation for high-volume, transactional client-server applications. Go and rev up your DDF workload.