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.

DB2 10 (and beyond) for z/OS: Being Smart About More RELEASE(DEALLOCATE)

By now, a lot of you probably know that a really important action to take, once your DB2 subsystem is at the Version 10 (conversion mode) level, is to rebind your plans and packages. Taking this action delivers several benefits:

• It tends to improve the CPU efficiency of package execution. Obviously, you could see performance gains related to positive access path changes, as the optimizer gets smarter with each new release of DB2; however, even if access paths associated with a package don't change, CPU consumption reduction is quite likely to be achieved through a DB2 10 rebind. Why? Because the package code so generated -- including the compiled, executable form of static SQL statements -- is more CPU-efficient than the pre-DB2 10 version of same.
• It causes package table virtual storage to be allocated from an agent local pool in the DB2 DBM1 address space, versus the EDM pool. The package table is the space into which a package is copied from the skeleton package table when it is executed (there will be a copy of a package in the package table for every thread to which the package is concurrently allocated). Because package table space no longer comes from the EDM pool after a package has been bound or rebound in a DB2 10 system, a latch formerly needed to serialize acquisition and release of EDM pool space for package table use is no longer needed for that purpose, and that's good for application throughput (latch reduction and elimination is a significant factor in the DB2 10 scalability story).
• It causes almost all thread-related virtual storage to go above the 2GB "bar" in DBM1.

That last item is noteworthy for two reasons: 1) it means that a DB2 10 subsystem can support a much higher number of concurrently active threads versus a DB2 8 or 9 environment, and 2) it means that with DB2 10 you have a lot more virtual storage "head room" than before to accommodate expanded use of the RELEASE(DEALLOCATE) option of BIND and REBIND PACKAGE. The rest of this blog entry is focused in the latter of these two points.

Binding a package with RELEASE(DEALLOCATE) can enhance the performance of associated applications, but you do NOT want to bind all of your packages in this manner; rather, you should be selective in looking to increase the number of RELEASE(DEALLOCATE) packages in a DB2 system. Below are some guidelines for your consideration. 

First, divide you search for candidate packages (i.e., packages currently bound with RELEASE(COMMIT) that could be advantageously rebound with RELEASE(DEALLOCATE)) into two courses: batch and online.

BATCH

Binding batch-executed packages with RELEASE(DEALLOCATE) can boost the performance of jobs that issue frequent commits in two ways: 1) as is the case for online transactions that reuse threads (more on this below), CPU consumption is reduced because the cost of releasing and then re-acquiring the same table space locks and package table elements at each commit is eliminated; and 2) additional CPU cycles can be saved through more effective leveraging of index lookaside (resulting in fewer GETPAGEs for index access) and sequential detection (which triggers dynamic prefetch). When RELEASE(COMMIT) is in effect, the execution efficiency benefits of index lookaside and sequential detection are diminished because the information tracked by DB2 in relation to these two performance features is reset at each commit, versus being retained across commits for packages bound with RELEASE(DEALLOCATE).

So, in reviewing your DB2-accessing batch programs, look for jobs that issue a lot of commits. If you use DB2 monitor-generated accounting reports as input to your analysis, note that information on commit frequency is available at the correlation name level (for batch applications, the job name is the DB2 correlation name -- you can request that your monitor generate accounting reports with data ordered by correlation name). One batch job might involve execution of several DB2-accessing programs and associated packages, so once you find a batch job that issues a lot of commits you might need to identify the related packages and then see if particular packages are the ones that drive a lot of commit activity (application developers might be able to help with that). Those packages, if currently bound with RELEASE(COMMIT), could potentially be rebound with RELEASE(DEALLOCATE) to good effect.

Considerations: Packages bound with RELEASE(DEALLOCATE) can get in the way of some package bind/rebind, DDL, and utility operations, so if you want to use RELEASE(DEALLOCATE) for batch packages you'll need to see when the associated jobs run (when a program with a RELEASE(DEALLOCATE) package is executed, the package will stay allocated to the batch job's thread until the job runs to completion) and make sure that you can, if needed, schedule package bind/rebind and DDL and utility operations (those that would affect RELEASE(DEALLOCATE) packages, or objects on which the RELEASE(DEALLOCATE) packages are dependent) around the batch job's scheduled run time (with DB2 11, there is relief in this area: a package's RELEASE status can be dynamically, automatically, and temporarily changed from DEALLOCATE to COMMIT to enable a bind or rebind or DDL or utility operation that would otherwise be blocked to proceed). Also, if you want to bind a batch-executed package with RELEASE(DEALLOCATE), check to see if it is executed through multiple different batch jobs versus just one -- again, you might need to schedule package bind/rebind and DDL and utility operations around the execution times of these jobs.

You'll probably want to ensure that RELEASE(DEALLOCATE) is not used for batch packages that issue LOCK TABLE statements or for batch packages that trigger lock escalation -- that could potentially lead to lock contention problems, because those exclusive table space locks would be retained until thread deallocation with RELEASE(DEALLOCATE) in effect (retention of table space-level locks for the duration of a thread is generally not an issue if the locks are of the intent variety, versus exclusive).

ONLINE -- CICS

First, look for CICS-DB2 packages associated with frequently executed transactions. As in analyzing a DB2-accessing batch workload, you could be helped in reviewing CICS-DB2 activity by looking at DB2 monitor-generated accounting reports that exclude non-CICS work and in which data is grouped at the correlation name level (that would be, for CICS programs, the CICS transaction name). Once the most frequently executed transactions are identified, you can determine the DB2 packages related to those transactions. In addition to frequency of execution, you should also look for transactions and packages with relatively low in-DB2 times, because it is for quick in-and-out transactions that the cost of repeatedly releasing and re-acquiring table space locks and package table elements is proportionately higher (versus transactions with larger in-DB2 CPU times). Also, keep in mind that RELEASE(DEALLOCATE) for CICS-DB2 packages won't affect performance if the threads through which the packages are executed are not reused. Typically, CICS-DB2 thread re-use is driven through the specification of some protected entry threads for transactions that are frequently executed (i.e., by making PROTECTNUM greater than 0 in the CICS DB2ENTRY resources associated with these transactions).

Considerations: As previously noted, DB2 11 provides relief for the problem of getting bind/rebind and DDL and utility operations to complete successfully when RELEASE(DEALLOCATE) packages and persistent threads (such as CICS-DB2 protected entry threads) are in the picture, but in a DB2 10 environment you'll need to think about whether this could be an issue for you (there are sites at which the execution volume of some CICS-DB2 transactions is sufficient to keep protected entry threads around for days). Give some thought to the following: if you're going into a time period during which you need to accomplish some bind/rebind and/or DDL and/or utility actions, and you feel that these actions might be blocked by RELEASE(DEALLOCATE) packages executed via CICS-DB2 protected entry threads, use CICS RDO (resource definition online) to dynamically take PROTECTNUM to 0 for related DB2ENTRY resources. This could have the effect of making RELEASE(DEALLOCATE) CICS-DB2 packages behave as though bound with RELEASE(COMMIT), if it were to cause the level of CICS-DB2 thread re-use to drop to none. Do your bind/rebind and/or DDL and/or utility work, then dynamically take PROTECTNUM values back to where they had been in order to get thread re-use going again.

ONLINE -- DDF

As with CICS-DB2 packages, frequency of execution plus relatively low in-DB2 CPU times are the key attributes for which you're searching. Candidates could be packages associated with stored procedures that are frequently executed through DDF. Also candidates are the IBM Data Server Driver and/or DB2 Connect packages, but you almost certainly DO NOT want RELEASE(DEALLOCATE) to be in effect for all applications that use these packages. To provide some control in this regard, bind the Data Server Driver and/or DB2 Connect packages into the default NULLID collection with RELEASE(COMMIT), and bind them with RELEASE(DEALLOCATE) into a collection with a different name. That way, you can make selective use of the RELEASE(DEALLOCATE) packages (and, therefore, make selective use of high-performance DBATs, which enable thread re-use for network-attached DB2-accessing applications) by pointing an application to the NULLID collection or the other collection via a data source property on the client side.

Considerations: If you need to shut down high-performance DBATs for a time to get some bind/rebind, DDL, or utility stuff done, do that by issuing the DB2 command -MODIFY DDF PKGREL(COMMIT). Then, when you're ready to re-enable high-performance DBATs, do so by issuing -MODIFY DDF PKGREL(BNDOPT).

Also, note that use of high-performance DBATs will decrease the number of DBATs in the DBAT pool, so you'll likely want to increase the value of MAXDBAT in ZPARM to help ensure that you continue to have a reasonable number of threads in your DBAT pool.

The bottom line

DB2 10 for z/OS gives you room, virtual storage-wise, to expand your use of the RELEASE(DEALLOCATE) package bind option. Taking this action can deliver performance dividends, but you'll want to be smart about it. Find the packages that would be best candidates for binding or rebinding with RELEASE(DEALLOCATE), and take steps to help ensure that a larger number of RELEASE(DEALLOCATE) packages won't lead to contention problems for bind/rebind, DDL, and utility operations. Oh, and measure the in-DB2 time for affected programs before and after you change packages to RELEASE(DEALLOCATE), so you'll have documented proof that the move paid off in the form of reduced in-DB2 times.

DB2 for z/OS: Answering Some Questions About Adding and Dropping Table Columns

From the very beginning, you've been able to add a column to an existing DB2 for z/OS table by way of the SQL statement ALTER TABLE with the ADD COLUMN option. In fact, relative ease of logical database design modification had a lot to do with DB2 becoming a very widely used DBMS. Of course, there are times when what you want to do is remove a column from an existing table. That proved to be a tougher nut to crack, functionality-wise, but we finally got ALTER TABLE DROP COLUMN with DB2 11 for z/OS. I'm not going to try to tell you everything there is to know about ALTER TABLE DROP COLUMN in this blog entry (you can find a very good write-up on this new feature in section 4.7 of the "red book" titled, IBM DB2 11 for z/OS Technical Overview). Instead, what I want to do here is address a couple of questions that could come to mind as you learn more about DROP COLUMN support.

Question: ALTER TABLE DROP COLUMN is a pending change. Is ALTER TABLE ADD COLUMN also a pending change in a DB2 11 environment?

Short answer: No

Longer answer: One of the really great enhancements delivered with DB2 10 for z/OS is something called pending DDL. Again, I won't try in this space to provide you with a pile of relevant information (check out section 4.1.3 of the IBM redbook titled, DB2 10 for z/OS Technical Overview), but here are two of the key aspects of pending DDL:

• It enabled non-disruptive implementation of a number of DB2 database design changes. Like what? Like changing a table space's page size, or DSSIZE, or SEGSIZE, or type (e.g., traditional segmented to universal partition-by-growth, or "classic" table-controlled partitioned to universal partition-by-range); or, with DB2 11, dropping a column from a table. How is "non-disruptive" pulled off? Simple: After the ALTER is executed, the target table space is placed in a new (with DB2 10) and non-restrictive advisory REORG-pending state labeled AREOR. A subsequent online REORG (SHRLEVEL REFERENCE or SHRLEVEL CHANGE) of the entire table space (not a partition-level REORG) materializes the outstanding pending changes associated with the table space.
• It enabled a "never mind" action with respect to the aforementioned database design changes. Suppose you act to change a table space's SEGSIZE via ALTER TABLESPACE, and then decide that you'd rather stick with the current SEGSIZE. Is that a problem? Not at all, especially if the online REORG that would materialize the pending change in segment size for the table space has not yet been executed. In that case, you'd just issue an ALTER TABLESPACE statement for the object in question, with the new (starting with DB2 10) DROP PENDING CHANGES option. Do that, and the pending changes -- which had been recorded by DB2 in the new (with DB2 10) SYSPENDINGDDL catalog table -- are deleted, the table space's AREOR status is removed, and you're back where you started.

[Note that with the exception of a change that results in the conversion of a non-universal table space to a universal table space (such as specifying MAXPARTITIONS in an ALTER TABLESPACE statement to convert a simple or traditional segmented table space to a universal partition-by-growth table space), pending changes can be made only to universal table spaces.]

So, in reading about ALTER TABLE DROP COLUMN for DB2 11, you could find yourself thinking, "Ooh -- if ALTER TABLE DROP COLUMN is a pending change, was ALTER TABLE ADD COLUMN also made a pending change in a DB2 11 environment?" As I indicated above, the answer to that question is, "No." ALTER TABLE ADD COLUMN remains in a DB2 11 system what it's always been: an immediate change. That doesn't mean that the column in question is immediately added to the table in a physical sense (that's accomplished by way of a subsequent REORG). It means that the table's definition in the DB2 catalog is immediately changed to reflect the addition of the new column.

This answer to the first question leads to the second question.

Question: OK, so ALTER TABLE ADD COLUMN is not a pending change. Even so, could the new ALTER TABLE DROP COLUMN functionality provided by DB2 11 be used to "undo" an ALTER TABLE ADD COLUMN action, before the column has been physically added to the table?

Short answer: Yes, but...

Longer answer: Yes, but you'll still need to execute an online REORG of the table space. You might think, "Why is that? I haven't yet executed a REORG to physically add the column named in the ALTER TABLE ADD COLUMN statement; therefore, the column is only logically there. Can't it just be logically removed?" The online REORG of the table space is required because ALTER TABLE DROP COLUMN is a pending change, and pending changes don't get acted upon until an online REORG is subsequently executed for the associated table space. This online REORG to put the ALTER TABLE DROP COLUMN into effect won't change the table in a physical sense -- it will just ensure that the logically added column (reflecting the ALTER TABLE ADD COLUMN action that you now want to un-do) is not physically added to the table.

Here's a sweetener for you: if you run an online REORG just to put pending changes into effect for a table space, and you don't want to incur the cost of re-establishing clustering sequence for table rows, DB2 11 provides a CPU-saving feature. Specify, in the utility control statement, SORTDATA NO (previously not permitted for a SHRLEVEL CHANGE online REORG) together with the new RECLUSTER NO option, and data WILL NOT be unloaded by way of the table's clustering index and WILL NOT be sorted in clustering sequence.

There is a lot of great stuff in DB2 11. This blog entry has highlighted a bit of that great stuff (and some of the great stuff introduced with DB2 10). More to come, so come back by.

DB2 for z/OS: Want to use High-Performance DBATs? Check your MAXDBAT Value

Of the features introduced with DB2 10 for z/OS, high-performance DBATs is one of my favorites. It enabled (finally) DDF-using applications to get the CPU efficiency benefit that comes from combining thread reuse with the RELEASE(DEALLOCATE) package bind option -- a performance tuning action that has long been leveraged for CICS-DB2 workloads. Implementing high-performance DBATs is pretty easy: in a DB2 10 (or 11) environment, when a package bound with RELEASE(DEALLOCATE) is executed by way of a DBAT (i.e., a database access thread -- the kind used for DRDA requesters that connect to DB2 via the distributed data facility), that thread becomes a high-performance DBAT (if it isn't one already). Before jumping into this, however, you should consider some things that are impacted by the use of high-performance DBATs. One of those things is the DBAT pool. That's where the MAXDBAT parameter of ZPARM comes in, and that's what this blog entry is about.

The value of MAXDBAT determines the maximum number of DBATs that can be concurrently active for a DB2 subsystem. The default value is 200, and at many sites that value, or one that's a little larger, has effectively supported a much greater number of DB2 client-server application connections (the default value for CONDBAT in ZPARM -- the maximum number of connections through DDF to a DB2 subsystem -- is 10,000). How so? Well, if your system is set up to allow for inactive connections (CMTSTAT = INACTIVE has been the default in ZPARM since DB2 V8), when a DDF transaction completes the associated connection will go into an inactive state (a very low-overhead transition, as is the transition back to the active state) and the DBAT used for the transaction will go into the DBAT pool, ready to service another transaction. That can happen because a "regular" DBAT is only associated with a particular DB2 connection while it is being used to execute a request from said connection. Because it is common for only a small percentage of DDF connections to a DB2 subsystem to be active (i.e., associated with in-flight transactions) at any given moment, a large ratio of connections to DBATs has historically been no problem at all.

Bring high-performance DBATs into the picture, and things change. In particular, a high-performance DBAT, once instantiated, will remain dedicated to the connection through which it was instantiated until it's been reused by 200 units of work (at which point it will be terminated, so as to free up resources allocated to the thread). That high-performance DBAT, therefore, will NOT go into the DBAT pool when a transaction using the thread completes. When a request associated with another connection comes in (i.e., from a connection other than the one through which the high-performance DBAT was instantiated), the high-performance DBAT won't be available to service that request. Some other DBAT will have to be used, and guess what? If that DBAT isn't a high-performance DBAT, it will become one if the package associated with the incoming request (and that could be a DB2 Connect or IBM Data Server Driver package) was bound with RELEASE(DEALLOCATE). The DBAT pool thus becomes progressively smaller as high-performance DBATs are instantiated. Know what else happens? The number of active DBATs goes up -- maybe sharply. Why? Because a "regular" DBAT is active only while it is being used to execute a DDF transaction. A high-performance DBAT, on the other hand, is considered to be active as long as it exists -- that will be 200 units of work, as mentioned previously, and when a high-performance DBAT is waiting to be reused, it's an active DBAT.

This last point -- about the number of active DBATs potentially rising sharply when high-performance DBATs are utilized -- is illustrated by some information I recently received from a DB2 professional. At this person's shop, high-performance DBATs were "turned on" for a DB2 subsystem (the PKGREL option of the -MODIFY DDF command can be used as a "switch," telling DB2 to either honor RELEASE(DEALLOCATE) for packages executed via DBATs -- thereby enabling instantiation of high-performance DBATs -- or not), and the number of active DBATs for the subsystem went from the usual 60 or so to about 700. Because the MAXDBAT value for the DB2 subsystem was already at 750, these folks didn't run out of DBATs, but the pool of "regular" DBATs got pretty small. In response to the big increase in active DBATs seen when high-performance DBAT functionality was enabled, the MAXDBAT value for the DB2 system in question was increased to 2000. Was this OK? Yes: When packages are bound or rebound in a DB2 10 for z/OS environment, almost all thread-related virtual storage goes above the 2 GB "bar" in the DBM1 address space, and that allows for a 5- to 10-times increase in the number of threads that can be concurrently active for the DB2 subsystem.

So, if you're thinking about using high-performance DBATs (and you should), check your subsystem's MAXDBAT value, and consider making that value substantially larger than it is now. Additionally, take steps to enable selective use of high-performance DBATs by your network-attached, DB2-accessing applications. For programs that contain embedded SQL statements and, therefore, have their own packages (e.g., DB2 stored procedures -- both external and native), use RELEASE(DEALLOCATE) for the most frequently executed of these packages. For the packages associated with DB2 Connect and/or the IBM Data Server Driver, use two collections: The default NULLID collection, into which you'd bind the DB2 Connect and/or IBM Data Server Driver packages with RELEASE(COMMIT), and another collection (named as you want) into which you'd bind these packages with RELEASE(DEALLOCATE). Then, by way of a data source or connection string specification on the client side, direct DDF-using applications to NULLID or the other collection name, depending on whether or not you want high-performance DBATs to be used for a given application.

To keep an eye on DBAT usage for a DB2 subsystem, periodically issue the command -DISPLAY DDF DETAIL. In the output of that command you'll see a field, labeled QUEDBAT, that shows the number of times (since the DB2 subsystem was last started) that requests were delayed because the MAXDBAT limit had been reached. If the value of this field is non-zero, consider increasing MAXDBAT for the subsystem. You might also want to look at the value of the field DSCDBAT in the output of the -DISPLAY DDF DETAIL command. This value shows you the current number of DBATs in the pool for the subsystem. As I've pointed out, maintaining the "depth" of the DBAT pool as high-performance DBAT functionality is put to use might require increasing MAXDBAT for your DB2 subsystem.

DDF activity can also be tracked by way of your DB2 monitor. I particularly like to use a DB2 monitor-generated Statistics Long Report to see if the connection limit for a DB2 subsystem (specified via the CONDBAT parameter in ZPARM) is sufficiently high. In the section of the report under the heading "Global DDF Activity," I'll check the value of the field labeled CONN REJECTED-MAX CONNECTED (or something similar -- fields in reports generated by different DB2 monitors might be labeled somewhat differently). A non-zero value in this field is an indication that the CONDBAT limit has been hit, and in that case you'd probably want to set CONDBAT to a larger number to allow more connections to the DB2 subsystem.

So there you go. Using high-performance DBATs can improve the CPU efficiency of your DB2 for z/OS client-server workload, but if you do leverage high-performance DBAT functionality then you might need to boost the DBAT limit for your DB2 subsystem in order to maintain the depth of your DBAT pool, because as high-performance DBATs increase in number, pooled DBATs decrease in number (unless you've upped your MAXDBAT value to compensate for this effect). Boosting MAXDBAT in a DB2 10 (or 11) environment is OK, as thread-related virtual storage in such an environment is almost entirely above the 2 GB "bar" in the DBM1 address space (assuming that packages have been bound or rebound with DB2 at the Version 10 or 11 level). Of course, you need real storage to back virtual storage, so if you increase the MAXDBAT value keep an eye on the z/OS LPAR's demand paging rate and make sure that this doesn't get out of hand (if the demand paging rate is in the low single digits or less per second, it's not out of hand).

DB2 for z/OS: Monitoring Prefetch Read Activity

Not long ago, a colleague sent to me some DB2 for z/OS buffer pool activity data that a DB2-using organization had sent to him. The DB2 people at this site were alarmed by some of the prefetch-related numbers in the data, and they had requested a review of the information. My analysis of the figures suggested that the system administrators were concerned by numbers that were in fact positive in nature; however, I also saw prefetch items that really were troublesome, and I subsequently learned that these were likely due to a buffer pool configuration change that had been made to improve application performance but ended up working towards the opposite end. In this blog entry I'll explain why buffer pool statistics related to prefetch activity are sometimes misinterpreted, and how "tuning" actions intended to bolster synchronous read performance can have unexpectedly negative consequences.

First, the misinterpreted figures. A system administrator at the aforementioned DB2 for z/OS site was doing a good thing: reviewing the output of the DB2 command -DISPLAY BUFFERPOOL(bpname) DETAIL for two of the key buffer pools allocated in a production subsystem. He calculated some ratios, and was particularly concerned about a couple of the calculated values. For both of the pools, the ratio of pages read via dynamic prefetch to the number of dynamic prefetch requests was quite low: about 2 to 1 for one pool, and only 0.13 to 1 for the other pool. This had the appearance to the system administrator of something being not right, as he knew that a dynamic prefetch request would generally be for 32 pages.

In fact, these dynamic prefetch requests versus pages read via dynamic prefetch numbers were just fine, but they are often viewed incorrectly. One factor that contributes to these incorrect readings of -DISPLAY BUFFERPOOL data is the fact that many people look at prefetch requests when they should be focusing instead on prefetch I/Os. The really important thing to understand here is this: a prefetch request is just that: a request to read from disk a particular set of pages (typically 32 contiguous table space or index pages in the case of dynamic prefetch). If all of the pages associated with a prefetch request are already in the buffer pool, the prefetch request will not result in a prefetch I/O. What that should tell you is, a high ratio of prefetch requests to prefetch reads is a good thing -- it means that pages are being effectively cached in the buffer pool, so that when a chunk of said pages is asked for by way of a prefetch request, an associated I/O will often not be necessary because all of the pages in the prefetch request are already in memory. Sure, a prefetch I/O will often be preferred to a synchronous I/O, because the latter always results in application wait time, while in the case of the former it is hoped that the prefetch I/O operation will complete before the application process (the one on behalf of which the I/O is being driven) asks for rows (or index entries, as the case may be) from the pages being read from disk. Even so, however, a "non-I/O" is preferable versus a prefetch I/O.

Pages read via prefetch, then, should be compared to prefetch I/Os, not to prefetch requests. Looked at in that light, pages read via dynamic prefetch for the buffer pools of interest here might still appear to be on the low side: about 13 pages per prefetch I/O for the one buffer pool and about 14 pages per prefetch I/O for the other pool. It would be better if those figures were closer to 32, the size of a typical dynamic prefetch request, right? WRONG. It's the same thing as a high ratio of prefetch requests to prefetch read I/Os: fewer pages read into memory per prefetch I/O is a good thing. It means that (again) pages are being effectively cached in the buffer pool, so that when a prefetch request for 32 pages (for example) is issued, far fewer than 32 pages will have to be read into memory from disk because close to 20 of those 32 pages (on average for these particular buffer pools during a particular time period) are already in the buffer pool.

Thus it is that the high ratio of dynamic prefetch requests to dynamic prefetch read I/Os seen by the system administrator for the two buffer pools in his DB2 system, and the low ratio of pages read via dynamic prefetch to dynamic prefetch read I/Os, are not "uh-oh" numbers -- they are "Yes!" numbers. At the same time, however, there were indeed some "uh-oh" numbers in the -DISPLAY BUFFERPOOL output that caught my eye. Specifically, I saw that prefetch had been disabled for one of the pools, due to a lack of buffer resources, 164 times during the 28 minutes of activity captured in the command output. [The output of -DISPLAY BUFFERPOOL DETAIL contains a timestamp for each buffer pool listed, showing the start of the activity-capture time period (the end of the period is the time at which the command was issued). This timestamp will indicate either the time at which a pool was last allocated, or when the -DISPLAY BUFFERPOOL command was previously and most recently issued for the pool; so, if the command is issued once for a pool and then issued again for the pool an hour later, the timestamp value in the output of the second issuance of the command will be one hour before the time at which the command was issued for the second time.] That's not good. Because prefetch reads, when appropriate, are generally preferred over synchronous reads (this because, as previously mentioned, they are "anticipatory" reads, intended to bring pages into memory before they are needed by an application process), you really don't want to see prefetch disabled because of a shortage of buffers available to support prefetch reads.

Why would there be such a buffer shortage for this pool? I had a suspicion, and that was confirmed when I saw that the sequential threshold for the pool (also known as VPSEQT, short for the virtual pool sequential threshold) was set at 25, versus the default value of 80. What did that mean? It meant that instead of the usual 80% of the pool's buffers being available to hold pages brought into memory via prefetch, only 25% of the buffers were available for this purpose (the other 75% of the buffers in the pool were reserved exclusively for pages read into memory via synchronous reads). Why had the sequential threshold been lowered for the pool? The thinking had been that this action would reduce synchronous read activity (and we like to reduce that because, as I pointed out, a synchronous read always involves application wait time) by providing more synchronous-read-only buffers in the pool. Sounds logical, right? And, it might have had the desired effect if there hadn't been a lot of prefetch requests for the pool. In fact, there were more than 800 prefetch requests per second for this pool, and dramatically reducing the buffer resources available to support prefetch I/Os associated with the prefetch requests ended up negatively impacting synchronous read activity -- precisely the opposite of the hoped-for effect.

Here's the connection between a non-zero value for PREFETCH DISABLED - NO BUFFER in -DISPLAY BUFFERPOOL output and synchronous read activity: when prefetch is disabled, the pages that would have been read into memory via a prefetch read I/O will likely still be sitting on disk when an application process needs them. What happens then? They are synchronously read into the buffer pool (while the application process waits). Thus, incidents of prefetch being disabled can increase synchronous read activity; furthermore, with far fewer buffers (versus the default) in this pool being available for holding prefetch-read pages, pages that had been brought into memory via prefetch were quickly being flushed out of the pool to make room for other prefetch-read pages, and that meant that they couldn't be re-referenced without driving more I/Os, and that also worked to drive up synchronous read activity for the pool.

So, I told these folks (through their local IBM technical specialist) to significantly increase the VPSEQT setting for this buffer pool, and explained the rationale behind the recommendation. They ended up changing the sequential threshold for the pool back to the default value of 80. The results (shown in the buffer pool section of a DB2 monitor statistics long report for the DB2 subsystem) were quite positive and, I think, interesting: the frequency of prefetch being disabled due to a lack of buffer resources went from about 6 per minute to zero, and synchronous read activity dropped by 43%, even though the percentage of the pool's buffers set aside exclusively for synchronous reads went from 75% to 20%. With the VPSEQT change, more pages could be read into memory in an anticipatory fashion, and those pages, once prefetched into the pool, were likely to stay resident in the pool for a longer period of time, and the combination of these effects reduced the need to synchronously read pages from disk.

Here are what I see as the key take-aways from this case:

• Do not discount the importance of prefetch reads. Some DB2 for z/OS people are overly focused on synchronous read activity, to the point that they will do things that negatively impact prefetch read operations for a buffer pool. That can be a mistake, leading in some cases to increased synchronous read activity.
• Be careful about reducing VPSEQT for a buffer pool. Such an adjustment might have an overall positive performance impact for a pool that has relatively little prefetch activity. Here, I'd look at the volume of prefetch requests (sequential, list, and dynamic) per second for the pool of interest. If that number is high (e.g., hundreds or more per second), DB2 has determined that asynchronous reads are the right choice for bringing many of the pages of objects assigned to the pool that are needed by programs into memory. If you squeeze down the resources with which DB2 can work in servicing prefetch requests, the result could be an undesirable increase in synchronous read activity. Even if you do think that you have a pool for which a lowering of the VPSEQT setting could be beneficial, don't go overboard. Don't change from VPSEQT=80 to some much-lower value in one fell swoop -- make more modest adjustments to VPSEQT and monitor results as you go along.
• Monitor buffer pool activity in an ongoing fashion, using information from your DB2 monitor and/or the output of the -DISPLAY BUFFERPOOL DETAIL command (a form of this command that I like to use is -DISPLAY BUFFERPOOL(ACTIVE) DETAIL, and I like to issue that command once and then again in an hour to have an hour's worth of activity captured in the output of the second issuance of the command). Look for incidents of PREFETCH DISABLED - NO BUFFER and PREFETCH DISABLED - NO READ ENGINE (the latter can also be related to a lack of buffer resources, if that lack of resources leads to elevated levels of prefetch read I/O activity). If you see non-zero values in these fields for a buffer pool, first check the VPSEQT value for the pool, and consider changing that value to 80 if it is less than 80. If you already have VPSEQT=80 for the pool, consider making the pool larger, if you have the real storage in the z/OS LPAR to back a larger pool.

If you're a DB2 person, prefetch is your friend. Don't hobble it.

A Great Analytics on System z Use Case: The Operational Data Store

A few months ago, I posted an entry to this blog on the subject of "moving queries to data," the idea being that when data to be analyzed for decision support purposes originates in a DB2 for z/OS database (as a great deal of the world's corporate and governmental data does), a very good case can be made for analyzing that data on its platform of origin -- System z -- as opposed to copying and moving the data to a different platform for querying and reporting. Quite recently I've had some communications with various individuals on this very topic, and these exchanges have led to my highlighting a particularly good use case for DB2 and System z as a data server for business intelligence applications: the operational data store, or ODS. The ODS is not a new concept, but I've found that it's not universally understood and leveraged. In this blog entry, I'll provide some information that I hope will clarify what an ODS is, for those not very familiar with the idea, and show why DB2 for z/OS can be an outstanding environment for an ODS, particularly when the operational data in question "lives" in a DB2 for z/OS system to begin with.

First, I think that it can be useful to contrast an ODS with a data warehouse. Keep in mind that having an ODS versus a data warehouse is not an either/or proposition -- an organization can make effective use of both. Speaking, then, in pretty broad-brush terms, here are some of the things that tend to differentiate these two types of analytics data stores (and when I use the term "operational database" hereafter, I'm referring to a source database that is accessed by operational, "run the business" online and/or batch applications):

• Whereas the design of a data warehouse database will usually differ from the design of the operational database(s) from which the data warehouse data is sourced (for example, the data warehouse might feature so-called fact and dimension tables arranged in what's known as a star schema, to facilitate certain types of online analytical processing), an ODS is very often an exact duplicate of an operational database (or at least a duplicate of a subset of the tables in the operational database) -- "duplicate" referring to table and column names, and table structure (i.e., column order and column data types). In some cases an ODS will differ from the associated operational database in terms of data retention (a company might decide to keep data for X amount of time in the operational database, and for a longer period of time in an ODS, for historical reporting purposes -- an arrangement that can work especially well if the older data is rarely, if ever, updated).
• Whereas a data warehouse is often sourced from several databases (which may be housed on different server platforms), an ODS commonly has one associated source database.
• Whereas data in a data warehouse is sometimes aggregated in some way, data in an ODS is typically "atomic-level" with regard to detail.
• Whereas there is often some tolerance for delay with respect to source data updates being reflected in a data warehouse (data extract/transform/load, aka ETL, processes that run on a nightly basis are not unusual for a data warehouse), users of an ODS often expect and may demand extremely low latency in this regard -- the requirement could even be for near-real-time ODS updates with respect to the updating of corresponding records in the source operational database.
• Whereas data in a data warehouse is often transformed in some way, to accomplish things such as making data fields (which might be code values in an operational source table) more user-friendly, data is typically NOT transformed when added to an ODS.

Given these differences, in the case of an ODS you will regularly find that instead of ETL, you simply have EL (extract/load, from the source database to the ODS), and that EL process may involve near-real-time replication. That being the case, when the source operational database is managed by DB2 for z/OS, it can make all kinds of sense to use DB2 for z/OS as well for the ODS. Different organizations do exactly that, in different ways. A company might have an ODS in the same DB2 for z/OS subsystem that is used for the operational database, with the ODS tables being in a different schema (that is to say, the tables in the ODS would have different high-level qualifiers versus the tables in the operational database). More commonly (in my experience), the ODS will be set up in a different DB2 subsystem. The DB2 subsystem used for the ODS could be in the same z/OS LPAR as the DB2 subsystem used for the operational database, but I more frequently find that it's in a different z/OS LPAR.

When an ODS sourced from a DB2 for z/OS database is itself managed by DB2 for z/OS, very low-latency replication of source data changes to the ODS is facilitated, as is management of the ODS environment itself (the DBA team that takes care of the DB2 for z/OS operational database will generally have a pretty easy time managing an ODS database that essentially mirrors the source database). This ease of management extends to matters related to data security -- the same DB2 for z/OS and RACF (or equivalent) security controls used to lock down data in the operational database can be leveraged to protect ODS data from unauthorized access. Another factor that contributes to the goodness of fit of DB2 for z/OS with an ODS is the nature of the typical ODS workload: some longer-running, complex and/or data-intensive queries, yes, but also a relatively high volume of quick-running, in-and-out "transactional queries" -- users wanting to see, immediately, the information pertaining to this particular customer, or that particular transaction, in the course of their data analytics work. DB2 for z/OS and System z have long excelled at handling such mixed workloads, while some other platforms used for analytics work can bog down in processing a large number of concurrently executing queries. If you want to make your DB2 for z/OS system even more highly capable with regard to "operational analytics" workloads, take a look at the DB2 Analytics Accelerator for z/OS -- technology that can preserve excellent performance for high-volume, transactional queries while dramatically speeding up the execution of more complex and data-intensive queries.

Here's another thought: when the idea of minimizing inter-platform data movement is taken to its extreme, you don't move the data at all -- you allow analytics users to query the actual operational tables in the production DB2 for z/OS database. This approach, while not commonplace, is utilized in some cases, and successfully. It is certainly technically feasible, and more so on the z/OS platform than others, thanks to the highly advanced workload management capabilities of z/OS. One interesting option in this area is available to organizations that run DB2 for z/OS in data sharing mode on a Parallel Sysplex: you route the analytics queries through a subset of the data sharing group members, and route the operational transactions and batch jobs through the other members of the group. All the SQL statements hit the same DB2 tables, but because the analytics and operational SQL statements run in different DB2 subsystems (which can be in different z/OS LPARs on different System z servers in the Sysplex), the analytics and operational workloads don't compete with each other for server memory or processing resources. I have some personal experience with such a set-up, and I wrote a few blog entries that provide related information: a part 1 and part 2 entry on running OLTP and business intelligence workloads on the same DB2 for z/OS system (written while I was working as an independent DB2 consultant), and an entry that describes technology that can be used to limit different workloads to different subsets of the members of a DB2 data sharing group.

Does your organization use DB2 for z/OS to manage and protect your most valuable data: the data generated by your run-the-business operational applications -- the data that you own? If so, do you have a DB2 for z/OS-based ODS that provides users with secure, high-performance access to a current version of that data with atomic-level detail? If you don't have such an ODS, consider how a data store of this nature, managed by DB2 for z/OS -- the same DBMS in which your "gold" data is housed -- could provide users throughout your business with a resource that would enhance decision making effectiveness and improve outcomes. Lots of organizations have taken this path. It may be one that your company should take, as well.

DB2 for z/OS Work: the Task's the Thing

To understand how DB2 work is handled in a z/OS system, you need to have some understanding of the tasks used to manage that work. Here, "tasks" doesn't refer to things done or to be done. It refers instead to the control blocks used to represent, for dispatching purposes, application and system processes in a z/OS LPAR. I have found that several misconceptions related to DB2 for z/OS workload processing are rooted in misunderstandings of the tasks behind the workload. In particular, I've seen situations in which folks have the wrong idea about DB2 DDF address space CPU consumption, about batch- versus online-issued SQL statements, and about zIIP eligibility of DB2 work, and in each case clarity came from a realization of the tasks related to the DB2 activity in question. In this blog entry, I'll try to provide you with some information that I hope will be helpful to you in understanding why DB2 for z/OS work is processed as it is.

DB2 DDF address space CPU consumption

With respect to control blocks used to manage DB2 work in a z/OS system, there are at least a couple of relevant dichotomies. First, you have "system" tasks and "user" tasks. The system tasks are associated with DB2 address spaces, and they generally have to do with work of a housekeeping variety (e.g., writes of changed data and index pages to disk), as well as things pertaining to the SQL statement execution environment (e.g., thread creation and termination). Where things can get a little confusing is in the area of DB2 DDF CPU utilization (DDF being the distributed data facility -- the DB2 address space through which SQL statements from network-attached application servers and workstations flow, and through which results flow in the other direction). The confusion to which I refer stems from the fact that the DDF address space has associated with it both system tasks and user tasks, as opposed to having only the former. For SQL statements issued by "local" applications (those that attach directly to a DB2 subsystem in the same z/OS LPAR), the user tasks -- those under which SQL statements execute and to which the large majority of CPU time consumed in SQL statement execution is charged -- belong to "allied" address spaces (i.e., the address spaces in which the SQL-issuing programs run). So, for example, the user task under which a SQL statement issued by a local CICS transaction program executes is the CICS subtask TCB associated with that transaction, and the user task under which a SQL statement issued by a local batch job executes is the TCB of the batch address space (more on TCBs in a moment). Compared to the user tasks of allied address spaces connected to a local DB2 subsystem, the system tasks of the DB2 IRLM (lock manager), MSTR (system services), and DBM1 (database services) address spaces consume relatively little CPU time.

In the case of the DDF address space, you have to keep in mind that the programs issuing the SQL statements aren't running in the z/OS LPAR with DB2, and yet they must be represented locally in the z/OS LPAR so that they can be properly prioritized and dispatched by the operating system. The local representation of a remote DB2-accessing program is a so-called preembtable SRB in the DDF address space (more on SRBs to come), and because that task is the local representation of the remote program, most of the CPU time associated with execution of SQL statements issued by that program will be charged to the DDF preemptable SRB. That is why DDF CPU consumption will be significant if there are a lot of SQL statements flowing to DB2 through the DDF address space -- it's analogous to a CICS region consuming a lot of CPU time if a lot of SQL statements are sent to DB2 by transactions running in that CICS region. The DDF user tasks are charged with a lot of the CPU time related to SQL statement execution, while the DDF system tasks consume only a small amount of CPU time. You can find more information about DDF CPU consumption in an entry I posted to this blog last year.

Batch- versus online-issued SQL statements

Not long ago, I got a note from a software analyst who was concerned that SQL statements issued by batch programs would get in the way of SQL statements issued by concurrently executing CICS transactions. He was specifically worried about "inefficient" batch-issued SQL statements (which could be thought of as long-running SQL statements, though long-running SQL statements are not necessarily inefficient in any way) getting in line for CPU cycles ahead of CICS-issued SQL statements. The main issue in this person's mind was the dispatching priority of the DB2 address spaces: a priority that was somewhat higher (as recommended) than that of the CICS regions in his system. If DB2 has a really high priority in the z/OS LPAR, won't long-running, batch-issued SQL statements negatively impact the performance of CICS transactions?

The answer to that question (unless the site's workload manager policy is unusual) is, "No" (or at least, "No, CICS transactions will not be behind these long-running, batch-issued SQL statements in the line for CPU cycles"). Again, the task's the thing. A SQL statement (long-running or otherwise) issued by a batch job runs under that job's task (though it executes in the DB2 database services address space, as do all SQL statements), and it therefore has the priority of that task. A SQL statement issued by a CICS transaction runs under that transaction's task in the associated CICS region, and so it executes with the priority of the transaction's task. Assuming that batch jobs in your environment have a lower priority than CICS transactions, SQL statements issued by batch jobs will have a lower priority relative to SQL statements issued by CICS transactions.

The priority of DB2 address spaces (which should be really high) does NOT impact the priority of SQL statements that access DB2 data. Why, then, is it important for the DB2 address spaces to have a high priority in the system? Because a LITTLE BIT of the work related to SQL statement execution is done under DB2 tasks (examples include data set open and close, and lock management), and if this work doesn't get done RIGHT AWAY as needed, the whole DB2-accessing workload can get seriously gummed up. That's why giving the DB2 address spaces a higher priority (doesn't have to be way higher) than DB2-connected CICS regions is good for CICS-DB2 throughput: it enables DB2 to very quickly take care of the little bit of work done by DB2 tasks in a very timely manner, so that the bulk of SQL statement processing (which, again, happens at the priority of SQL statement-issuing programs) won't get bogged down waiting for locks to be released or threads to be created or whatever. More information on DB2 and CICS address space priority recommendations can be found in a blog entry on the topic that I posted last year.

zIIP eligibility of DB2 work

I mentioned in the second paragraph of this entry that there are a couple of dichotomies with regard to the control blocks that are used for tracking and dispatching work in a z/OS system. The first of these -- system tasks and user tasks -- I covered already. The second dichotomy I have in mind is TCBs and SRBs, or, more formally, task control blocks and service request blocks. For many years, people associated TCBs with user tasks, and SRBs with system tasks. That thinking wasn't far off the mark until the 1990s, when, needing a mechanism to manage work such as that processed through the DB2 DDF address space, IBM z/OS developers delivered a new type of SRB -- an SRB that, as said by a then-DB2 DDF developer, "acts like a TCB." This was the enclave SRB, and in particular, the preemptable SRB.

It's important to keep in mind that SQL statements that get to DB2 via DDF execute under preemptable SRBs. Here's why that's important: work that runs under such tasks is zIIP eligible (zIIPs are System z Integrated Information Processors -- the "specialty engines" that provide very economical computing capacity for certain types of work). In the case of DDF-routed SQL, the zIIP offload percentage tends to be about 60% (queries parallelized by DB2 also run under preemptable SRBs, and so are zIIP eligible, as I pointed out in a blog entry I posted back in 2010).

In my experience, people are most likely to get confused about zIIP eligibility of DB2 work when they are thinking about native SQL stored procedures. First, they may wonder why a native SQL procedure is zIIP-eligible when called by a DRDA requester (i.e., when invoked via a CALL that goes through the DDF address space), but not zIIP-eligible when called by a local-to-DB2 program (such as a CICS transaction). People can also be a little unclear on the reason why a native SQL procedure called by a DRDA requester is zIIP-eligible, while an external stored procedure (such as one written in COBOL or C) called by a DRDA requester is not zIIP-eligible. To get things straight in both of these cases, remember (again) that the task's the thing. A native SQL procedure runs under the task of the application process through which it was invoked, while an external stored procedure runs under a TCB in a WLM-managed stored procedure address space; thus, a native SQL procedure, when called by a DRDA requester, will run under a preemptable SRB in the DDF address space (as will any SQL statement issued by a DRDA client program), and that is why the native SQL procedure will be zIIP eligible in that situation. When the same native SQL procedure is called (for example) by a CICS transaction program, it will run under that program's task. Because that task is a TCB (in the associated CICS region), the native SQL procedure will not be zIIP eligible when so called. Similarly, even if an external stored procedure is called by a DRDA requester, it will run under a TCB (in a WLM-managed stored procedure address space) and so will not be zIIP eligible.

If you'll keep in mind the type of task -- user or system, TCB or SRB -- involved in getting various types of DB2 work done, you'll have a much better understanding of how that work is managed and prioritized, and whether or not it is zIIP-eligible. I hope that the information in this blog entry will be useful to you in that regard.