A Case Study - Db2 for z/OS High-Performance DBATs

Over the years, I have posted several entries to this blog that have covered various aspects of Db2 for z/OS high-performance DBATs (examples are entries posted in 2016 and 2013). Over the past couple of weeks, I have been working with a Db2 for z/OS administrator who is engaged in implementing high-performance DBAT functionality for the first time at his site. That implementation effort has involved a good bit of back-and-forth communication between myself and the Db2 administrator, and I saw that it makes for a good case study that touches on many of the issues associated with high-performance DBAT set-up, monitoring and usage. Accordingly, I decided to use this case study as the basis for a blog entry.

OK, so first there is the matter of getting high-performance DBAT functionality in the first place. How do such DBATs come into being (and a DBAT, or database access thread, is the kind of thread used for an application process that accesses Db2 data by way of the distributed data facility, or DDF)? There are two requirements here:

1. A package or packages bound with RELEASE(DEALLOCATE). The CPU efficiency boost provided by high-performance DBATs is attributable to RELEASE(DEALLOCATE) packages that stay allocated to such a thread, and to parent locks (table space- or partition-level locks) that, once acquired, also stay allocated to the thread, until the thread is terminated after having been reused by 200 transactions (parent locks acquired in the execution of dynamic SQL statements issued through a RELEASE(DEALLOCATE) package are retained until thread termination if dynamic statement caching is in effect). If the same packages would be used for all - or at least most - of the 200 transactions that reuse a high-performance DBAT, and if the same table spaces would be accessed by all - or at least most - of the thread-reusing transactions, what we save is the CPU that would be consumed in releasing and reacquiring for the thread the same packages and table space locks, over and over again when one transaction completes and another reuses the thread (keep in mind that parent locks are almost always of the "intent" variety, and intent locks do not conflict with each other, so we do not worry about retaining them for a thread as transactions reusing a thread come and go).
2. A DDF PKGREL setting of BNDOPT. The PKGREL parameter of a Db2 subsystem's distributed data facility is the "on/off switch" for high-performance DBATs. The parameter's value is set via execution of a -MODIFY DDF command, and when PKGREL = BNDOPT then the high-performance DBAT switch is "on," and when PKGREL = COMMIT the switch is in the "off" position (another PKGREL setting, BNDPOOL, can be appropriate when application servers disconnect from a Db2 system soon after connecting, versus the more common scenario in which servers connect to Db2 and stay connected for a long time). Why this on/off switch? Because it is possible that a RELEASE(DEALLOCATE) package allocated to a high-performance DBAT could get in the way of a database administration task such as an ALTER of a database object on which the package is dependent. If that situation were to present itself, a Db2 DBA could issue -MODIFY DDF PKGREL(COMMIT), wait for the existing high-performance DBATs to go away (that should not take more than about 4 minutes), perform the database administration function while DDF transactions continue to execute using "regular" DBATs, and then turn high-performance DBAT functionality back on by issuing -MODIFY DDF PKGREL(BNDOPT).

Next question: which package or packages should be bound with RELEASE(DEALLOCATE) for the purpose of getting high-performance DBAT functionality? If you want a certain DDF application to use high-performance DBATs, and that application calls Db2 stored procedures, one option would be to bind the packages of those stored procedures (or at least the ones that are called most frequently) with RELEASE(DEALLOCATE). The DDF application workload at the site I referenced above involves very little in the way of stored procedure calls, so the Db2 administrator decided to get high-performance DBAT functionality by binding the IBM Data Server Driver (formerly Db2 Connect) packages with RELEASE(DEALLOCATE). The default collection for those packages is called NULLID. Did the Db2 administrator bind the packages in the NULLID collection with RELEASE(DEALLOCATE)? No. Why? Because doing that would cause all DDF applications (more specifically, all such applications that are DRDA requesters, as opposed to applications that use the REST interface to DDF) to use high-performance DBATs, and that would likely be a sub-optimal situation. What you want is to utilize high-performance DBATs for selected DDF applications. How do you do that? You do what the Db2 administrator did: you BIND COPY the IBM Data Server Driver packages into another collection, specifying RELEASE(DEALLOCATE) in so doing, and then you point a DDF application for which you want high-performance DBAT usage to the alternate collection of IBM Data Server Driver packages using the Db2 profile tables (this technique is described in an entry I posted to this blog a couple of years ago).

Time for a test! The Db2 administrator verified that PKGREL was set to BNDOPT in a development Db2 environment, and then he got some DDF activity going in that environment, with applications accessing Db2 using the IDs for which profiles had been created to point the applications to the collection in which the IBM Data Server Driver packages had been bound with RELEASE(DEALLOCATE). He used his Db2 monitor to check on high-performance DBAT usage, and saw that there was no high-performance DBAT activity whatsoever on the system - evidenced by the big fat zero in the DDF statistics field labeled by his monitor as "Active DBATs Bound with RELEASE(DEALLOCATE)" (note that different Db2 monitors give this field a slightly different label). What was going on? Why was there no high-performance DBAT activity? There was no such activity because the Db2 administrator had overlooked one thing: after one or more profiles have been defined using the Db2 profile tables, they have to be activated if you want them to be in effect. The Db2 administrator issued the command -START PROFILE to load the contents of the profile tables into memory, thereby activating all profiles for which PROFILE_ENABLED had been set to Y in DSN_PROFILE_TABLE, and after doing that he saw that high-performance DBATs were indeed being used in the development Db2 system (note that profiles can be automatically activated by setting the ZPARM parameter PROFILE_AUTOSTART to YES - the default value for that ZPARM is NO).

OK, on to the production environment. The Db2 administrator BIND COPY-ed the IBM Data Server Driver packages from NULLID into an alternate collection, with a specification of RELEASE(DEALLOCATE); set up the appropriate Db2 profiles and attributes (and activated them!); made sure that the DDF PKGREL setting was BNDOPT (verified in the output of a -DISPLAY DDF DETAIL command); and... saw no use of high-performance DBATs. Huh? What was going on? Why were high-performance DBATs not used? Here's why: when an application connects to a Db2 system via DDF, it picks up its default package collection at connection time, and in the production environment at this site - as at many sites - DDF-using applications connect to Db2 and stay connected for a long time. Those applications were already connected to the Db2 system when the alternate IBM Data Server Driver package collection was established and profiles were set up and activated to point applications to that collection. The applications that the Db2 administrator wanted to direct to the alternate IBM Data Server Driver package collection would not use packages in that collection until they established new connections to the Db2 system. The Db2 administrator knew that the Db2 system was going to be "bounced" (stopped and restarted) during the upcoming weekend, and he figured - rightly - that when applications subsequently reconnected to the Db2 system they would be directed to the alternate IBM Data Server Driver package collection (the one with the packages bound with RELEASE(DEALLOCATE)) thanks to the Db2 profiles he has set up and activated.

One more plot twist. The Db2 Administrator had bound the packages in his alternate IBM Data Server Driver package collection with RELEASE(DEALLOCATE) and with KEEPDYNAMIC(YES). I explained to him that I generally view those two specifications as either/or options, versus both-together options. In other words, I would bind the IBM Data Server Driver packages in an alternate collection with either RELEASE(DEALLOCATE) or KEEPDYNAMIC(YES), and with that choice before me I would go with RELEASE(DEALLOCATE) in a large majority of cases (the packages in the NULLID collection should be bound with RELEASE(COMMIT) and KEEPDYNAMIC(NO)). KEEPDYNAMIC(YES) has some baggage associated with it (as explained in an entry I posted to this blog in 2016 - scroll down near the bottom of that entry), and getting maximum performance benefit from KEEPDYNAMIC(YES) requires programs to be coded with KEEPDYNAMIC(YES) in mind - and most are not - and I really like the on/off switch that we have for high-performance DBATs (provided, as previously mentioned, by the Db2 DDF PKGREL parameter).

The Db2 administrator agreed that the packages in his alternate IBM Data Server Driver package collection should be bound with RELEASE(DEALLOCATE) and with KEEPDYNAMIC(NO), and he wanted to rebind the packages with those specifications but found that he could not do that because the packages were in use (note: the rebind phase-in functionality provided by Db2 12 function level 505 can take care of that situation). This "in-use" situation surprised him, because he had set PKGREL for the Db2 system to COMMIT to turn off high-performance DBAT functionality (he planned to turn high-performance DBAT functionality back on after rebinding the packages in the alternate IBM Data Server Driver package collection), and following the PKGREL change he saw that the number of high-performance DBATs did indeed go to zero for the system. Why, then, were the packages in what he thought of as his high-performance DBAT collection still being used? I told the administrator that PKGREL does NOT affect the package collection that a DDF application uses; rather, it determines whether the RELEASE specification of packages allocated to DBATs for execution will be honored, if that specification is DEALLOCATE. If a DDF application is pointed to collection COLL_XYZ, and the packages in that collection are bound with RELEASE(DEALLOCATE), those packages will be used if PKGREL is set to COMMIT. In that case, however, the packages will be treated by Db2 as though they had been bound with RELEASE(COMMIT). If the PKGREL setting is changed to BNDOPT, the RELEASE(DEALLOCATE) bind option (for which BNDOPT is short) will be honored.

And there you have it. One high-performance DBAT-related case study that touches on so many different aspects of this Db2 for z/OS feature. I hope that the information provided in this blog entry will be useful for you.