DB2 for z/OS: Clearing Up Some Matters Pertaining to Database Access Threads

I have recently received a number of questions pertaining to DB2 for z/OS database access threads, or DBATs. DBATs are threads used in the execution of SQL statements that are sent to DB2 from network-attached applications (i.e., from DRDA requesters that access DB2 for z/OS by way of DB2's distributed data facility, also known as DDF). Thinking that these questions (and associated answers) might be of interest to a good many people in the DB2 for z/OS community, I'm packaging them in this blog entry. Without further ado, here are the Qs and As.


Question: What makes a DBAT a high-performance DBAT?

Answer: This question actually come to me in reverse form, as (and I'm paraphrasing), "What makes a DBAT a 'regular' DBAT versus a high-performance DBAT?" The answer's pretty simple: a "regular" DBAT is one that is not a high-performance DBAT, and a high-performance DBAT is one to which at least one package bound with RELEASE(DEALLOCATE) has been allocated for execution; therefore, if a DBAT does not have a RELEASE(DEALLOCATE) package allocated to it then it is a "regular" DBAT. Note that this answer presupposes that high-performance DBAT functionality has been enabled on the DB2 subsystem in question (more on this below) -- if that has not happened then all DBATs will be of the "regular" variety because there cannot be any high-performance DBATs.


Question: Can I get high-performance DBATs by binding the IBM Data Server Driver (or DB2 Connect, if that's what you use) packages with RELEASE(DEALLOCATE)?

Answer: Yes, but you'll want to do that the right way. This question is of greatest interest to organizations having DDF application workloads characterized by little (if any) in the way of static SQL statements. Execution of a static SQL statement, of course, involves execution of code in a DB2 package, and packages can be bound with RELEASE(DEALLOCATE), and, as noted above, executing a RELEASE(DEALLOCATE) package by way of a DBAT makes the DBAT a high-performance DBAT, if it wasn't such already. For a DDF-using application, static SQL-related packages could belong to stored procedures called by the application, or they might be associated with static SQL statements issued by the application itself -- for example, SQLJ statements embedded in a Java program. What if a DDF-using application uses dynamic SQL exclusively? That's quite common, as SQL statements issued through non-DBMS-specific interfaces such as JDBC and ODBC (both very widely used) are dynamically prepared and executed on the target DB2 for z/OS server. Are such applications blocked from the CPU efficiency gains that can be delivered via high-performance DBATs? No, they are not. Why not? Because even when a DDF-using application issues SQL statements that are exclusively dynamic from the DB2 perspective, packages are still used. Which packages? IBM Data Server Driver -- or, alternatively, IBM DB2 Connect -- packages (your license for the latter entitles you to use the former, and it's recommended that you use the former). Those packages are bound, by default, into a collection called NULLID; so, should you bind all the packages in the NULLID collection with RELEASE(DEALLOCATE), so as to make your DBATs high-performance DBATs? No, you should not do that. Why? Because that would cause ALL of your DBATs to be of the high-performance variety, and that would not be an optimal use of this functionality. You want to be smart about your use of RELEASE(DEALLOCATE) -- for DDF-using applications and for "local-to-DB2" applications, as well. That means, for a DDF workload, utilizing high-performance DBATs for frequently executed transactions that have a relatively small average in-DB2 CPU time (for such transactions, the proportional cost of repeatedly acquiring, releasing, and re-acquiring the same table space-level locks and package sections -- greatly reduced via high-performance DBATs -- is relatively significant versus transactions with higher in-DB2 times). You want, then, to use high-performance DBATs selectively. How can you do that? By binding the IBM Data Server Driver (or DB2 Connect) packages into the default NULLID collection with RELEASE(COMMIT), and into a collection not named NULLID with RELEASE(DEALLOCATE). THEN you can selectively use high-performance DBATs by pointing an application, by way of a client-side data source property, to the collection into which the Data Server Driver (or DB2 Connect) packages were bound with RELEASE(DEALLOCATE), when you want that application to use high-performance DBATs. And remember, by the way, to give consideration to upping your MAXDBAT value before starting to use high-performance DBATs.


Question: I'm using RELEASE(DEALLOCATE) packages with my DBATs, but my DB2 monitor indicates that I'm not getting high-performance DBAT usage. Why is that?

Answer: First of all, about this monitoring thing: check a DB2 monitor-generated statistics long report for your subsystem (or an online display of DDF activity provided by your monitor), and in the DDF activity section of the report, look for the field that is labeled HWM ACTIVE DBATS-BND DEALLC (or something like that -- field names can differ slightly from one DB2 monitor to another). If that field shows zero, your DDF applications are not using high-performance DBATs. If you see a zero in that field and think that you shouldn't, because you have RELEASE(DEALLOCATE) packages being executed via DBATs, issue the command -DISPLAY DDF DETAIL on the DB2 subsystem of interest. In the output of that command, look for the line with message DSNL106I. If what you see there is PKGREL = COMMIT, you're not getting high-performance DBATs because your DDF is not enabled for high-performance DBAT functionality. To change that, issue the command -MODIFY DDF PKGREL(BNDOPT). As a result of this command being executed, DDF will honor the RELEASE(DEALLOCATE) specification for packages allocated to DBATs for execution, and you'll then see instantiation of high-performance DBATs in the system. Note that it is possible for certain BIND/REBIND, ALTER, and pending DDL-materializing online REORG operations to be blocked by a RELEASE(DEALLOCATE) package that is allocated to a persistent thread such as a high-performance DBAT. Even though DB2 11 delivered a RELEASE(DEALLOCATE) "break-in" feature to enable BIND/REBIND and ALTER and pending DDL-materializing online REORG operations to proceed in the face of RELEASE(DEALLOCATE) packages that would otherwise be in the way, it still may be necessary to issue -MODIFY DDF PKGREL(COMMIT) to temporarily "turn off" high-performance DBATs when you need to perform these operations. When you're done with the database administration actions, issue -MODIFY DDF PKGREL(BNDOPT) to turn high-performance DBAT functionality back on.


Question: I'm seeing more idle thread timeouts for my DBATs than I'd like. What can I do about that?

Answer: The best way to prevent DBATs from timing out due to inactivity is to let the connections that use DBATs go into an inactive state when DDF transactions complete (and I'm referring here to "regular" DBATs -- a high-performance DBAT will remain tied to the connection through which it was instantiated until it terminates, typically as a result of being reused 200 times). It is important to realize that an application's connection to DB2 going inactive at the end of a transaction is a GOOD THING -- it's a big part of the DB2 for z/OS client-server scalability story. The virtual storage footprint of an inactive connection is very small, and the CPU cost of switching a connection from an active to an inactive state and back again is very small. When an application's connection to DB2 goes inactive, the DBAT that it was using goes into a "disconnected" state (NOT inactive), which is to say that it goes into the DBAT pool, ready to be called up to service another transaction coming from some other connection. Pooled DBATs are not subject to the idle thread timeout limit (specified via the DB2 ZPARM parameter IDTHTOIN), and inactive connections don't time out. How do you get inactive connection functionality? First, the DB2 ZPARM parameter CMTSTAT needs to be set to INACTIVE (that's been the default value for a LONG time). Second, transactions using DBATs need to finish with a "clean" commit. Basically, this means that nothing is allocated to the DBAT when a transaction using the DBAT completes -- so, no locks of any kind, no WITH HOLD cursors left un-closed, no declared global temporary tables left with data in them, etc. The most important thing that goes into getting "clean" commits is the COMMIT itself. Some client-side application developers think that a COMMIT isn't needed for a transaction that only reads data. Not true. If a read-only DDF transaction doesn't issue a COMMIT, the associated DBAT will hold one or more table space-level locks, and that would be enough to keep the connection from going inactive and the associated DBAT from going back into the DBAT pool -- if the DBAT stays in a connected state because no COMMIT has been issued, and if the amount of time specified in the ZPARM parameter IDTHTOIN goes by with no activity for the thread, it will time out. Oh, and another thing: if you have client-side developers occasionally issuing SQL statements like SELECT 1 FROM SYSIBM.SYSDUMMY1 in a well-intentioned effort to keep connections to DB2 "alive," TELL THEM TO STOP DOING THAT. Such actions can keep DB2 connections from going inactive when transactions complete, and as I stated previously it is GOOD for DB2 connections to go inactive when DDF transactions finish processing.


Question: Can the KEEPDYNAMIC(YES) package bind specification lead to idle thread timeouts for DBATs?

Answer: Yes. Here's the deal: with KEEPDYNAMIC(YES) in effect, prepared dynamic SQL statements are kept in a thread-specific local cache. Reusing one of these locally cached dynamic SQL statements is even more CPU efficient than getting a hit in the global dynamic statement cache in the DB2 EDM pool. That's good, but here's the flip side: with KEEPDYNAMIC(YES) in effect, you'll have prepared dynamic statements allocated locally to the thread, and as I noted above, a DBAT cannot go into a disconnected state (into the DBAT pool) at the end of a transaction if something is allocated to the DBAT. With the DBAT thus prevented from going into the DBAT pool (because of the prepared statements allocated locally to the DBAT), it is subject to hitting the idle thread timeout limit and being cancelled for that reason. Now, in a couple of situations you get some relief from this stricture: if either Sysplex workload balancing or seamless failover is in effect (these are DB2 Connect or IBM Data Server Driver configuration options) then at commit time, if the only thing keeping a DBAT in the connected state is the local cache of prepared dynamic SQL statements that exists because KEEPDYNAMIC(YES) is in effect, the DBAT can go unused for 20 minutes before the DB2 idle thread checker will drive a cancellation of the thread. Also, when either of the aforementioned DB2 client configuration options is in effect (Sysplex workload balancing or seamless failover), and a DBAT associated with a KEEPDYNAMIC(YES) package is being repeatedly used (so that idle thread timeout does not occur), the DBAT can stay in the connected state for an hour before being terminated (this so that resources allocated to the DBAT will be periodically freed up). Given all this, my thinking on KEEPDYNAMIC(YES) can be summed up as follows: if you have a DDF-using application that is specifically designed to take advantage of the KEEPDYNAMIC(YES) bind option, you can certainly consider going that route to achieve related CPU savings; otherwise, my inclination would be to use KEEPDYNAMIC(NO), go for hits in the global dynamic statement cache (not quite as good, efficiency-wise, as reuse of a locally cached prepared statement, but still a whale of a lot better than full PREPAREs of dynamic statements), and use high-performance DBATs to achieve additional CPU savings.

That's all for now. I hope that this information will be of use to you.