DB2 for z/OS: Looking Again at RELEASE(DEALLOCATE) vs. RELEASE(COMMIT)
Quite a few years ago (probably 15 or more), I sat in a hotel meeting room during an IT conference and listened to a presentaion on mainframe DB2 performance delivered by IBM's Akira Shibamiya (still a DB2 performance guru extraordinaire). It was a typical Akira presentation, full of useful tips for improving the CPU efficiency of DB2 application programs. One of the tips offered: to reduce an SQL-issuing program's CPU consumption (by how much, I'll get to near the end of this blog entry), specify RELEASE(DEALLOCATE) instead of RELEASE(COMMIT) when binding the associated package (after consideration of some cautionary notes, such as the fact that tablespace locks are held until thread deallocation when RELEASE(DEALLOCATE) is used - a problem if a program explicitly requests an exclusive tablespace lock or if lock escalation occurs).
Good stuff. A nice addition to the performance-tuner's tool kit.
A couple of years or so after the aforementioned presentation event, IBM introduced DB2 data sharing on the mainframe platform. This led to renewed discussion of the RELEASE option of BIND PACKAGE among people implementing or evaluating the shared-data scale-out clustering technology. The reason: RELEASE(DEALLOCATE), used in conjunction with DB2 threads that would persist across commit operations (such as CICS protected entry threads), could significantly reduce a type of global lock contention called XES contention (as described in an earlier post to this blog). Less global lock contention meant less data sharing overhead, so RELEASE(DEALLOCATE) came to be quite widely used at data sharing sites.
Fast-forward to the present, and the matter of RELEASE(DEALLOCATE) versus RELEASE(COMMIT) has again come up for review. The impetus is two-fold: 1) some people who have been using RELEASE(DEALLOCATE) extensively (often for the purpose of reducing the incidence of XES global lock contention in a DB2 data sharing environment) are seeing related incidences of program failures caused by EDM pool-full conditions, and 2) the new global locking protocol introduced with DB2 for z/OS V8 removed the XES-contention-suppression incentive to use the RELEASE(DEALLOCATE) bind option (also covered in the previously-cited blog post).
About EDM pool-full: when a package is executed, it's loaded into DB2's EDM pool (EDM pool space used for sections of packages executed via threads is labeled PT - short for package table - in some monitor displays and reports). If there's not enough space in the pool for the package, the program will fail with a -904 SQL code (resource unavailable, with a code indicating that the unavailable resource is EDM pool space). Such failures due to pool-full conditions are also recorded in the EDM Pool section of a DB2 monitor statistics report or display. These failures tend to be rare, as there are usually enough free (empty) pages in the pool to accommodate new package load requests, and even when free pages are lacking there are typically occupied pages that can be stolen for the new load because the package sections in the occupied pages are associated with packages that were acquired for threads but which were subsequently released. This is where RELEASE(DEALLOCATE) can make a difference, because that bind specification indicates that the package, once acquired for execution via a thread, is not to be released until the thread is deallocated (as opposed to being released at a commit point). If a thread persists for a long time, packages executed via the thread and bound with RELEASE(DEALLOCATE) will be considered by DB2 to be in-use for a long time, and the EDM pool pages occupied by those packages will be non-stealable for a long time. That can lead to a pool-full situation if space in the EDM pool becomes tight.
So, how is it that an organization using RELEASE(DEALLOCATE) can experience a rash of EDM pool-full-related program failures seemingly "out of the blue," only to have the problem go away by itself a short time later? Here's how: RELEASE(DEALLOCATE) works just like RELEASE(COMMIT) when a thread is deallocated at commit time. That's what will happen to a non-protected CICS-DB2 thread if it is not reused, and such a thread will not be reused unless a transaction associated with the same DB2ENTRY CICS resource as the about-to-commit (and complete) transaction is queued up and waiting for a thread. If there are enough threads defined for the DB2ENTRY resource, or if there are enough pool threads and the resource definition indicates that a transaction should overflow to the pool if an entry thread is not available, transactions won't queue waiting for threads, and non-protected threads won't be reused (they'll instead be deallocated, freeing EDM pool space occupied by packages bound with RELEASE(DEALLOCATE) and executed by way of the thread. Folks usually have enough threads defined to prevent transaction queuing, but over time, if the transaction arrival rate increases and the CICS-DB2 thread specifications aren't updated, you could reach a point at which thread re-use suddenly jumps during a peak processing period, and if the re-used (and therefore persistent) CICS-DB2 threads use packages bound with RELEASE(DEALLOCATE), the EDM pool-full situation described above (brought on by a decline in the number of stealable EDM pool pages) can occur.
If this has happened at your site, you have several options available for eliminating the problem:
Some folks would conclude that a few extra percentage points of CPU consumption is an acceptable trade for an easily-administered program bind procedure, and they'd go with RELEASE(COMMIT) for everything. Others, in very high-volume environments in which CPU consumption gets a lot of attention, would decide to go for optimal efficiency via RELEASE(DEALLOCATE). Even these folks, however, should use RELEASE(DEALLOCATE) only on a selective basis - I would NOT recommend binding all programs with that specification. The best candidates would be high-use programs executed via persistent threads (again, meaning threads that persist across commits).
Shout-out: I've had many a discussion about RELEASE(DEALLOCATE) versus RELEASE(COMMIT) with my friend Anthony Ciabattoni, lead DB2 for z/OS systems programmer at CheckFree (part of Fiserv Corporation). Anthony will be sharing some of his experience with DB2 data sharing in an IBM Webcast to be broadcast on February 24. Should be a good one, in no small part because Anthony is as energetic as he is knowledgeable. You can get more information at this url: http://www-01.ibm.com/software/os/systemz/webcast/feb24/.
Good stuff. A nice addition to the performance-tuner's tool kit.
A couple of years or so after the aforementioned presentation event, IBM introduced DB2 data sharing on the mainframe platform. This led to renewed discussion of the RELEASE option of BIND PACKAGE among people implementing or evaluating the shared-data scale-out clustering technology. The reason: RELEASE(DEALLOCATE), used in conjunction with DB2 threads that would persist across commit operations (such as CICS protected entry threads), could significantly reduce a type of global lock contention called XES contention (as described in an earlier post to this blog). Less global lock contention meant less data sharing overhead, so RELEASE(DEALLOCATE) came to be quite widely used at data sharing sites.
Fast-forward to the present, and the matter of RELEASE(DEALLOCATE) versus RELEASE(COMMIT) has again come up for review. The impetus is two-fold: 1) some people who have been using RELEASE(DEALLOCATE) extensively (often for the purpose of reducing the incidence of XES global lock contention in a DB2 data sharing environment) are seeing related incidences of program failures caused by EDM pool-full conditions, and 2) the new global locking protocol introduced with DB2 for z/OS V8 removed the XES-contention-suppression incentive to use the RELEASE(DEALLOCATE) bind option (also covered in the previously-cited blog post).
About EDM pool-full: when a package is executed, it's loaded into DB2's EDM pool (EDM pool space used for sections of packages executed via threads is labeled PT - short for package table - in some monitor displays and reports). If there's not enough space in the pool for the package, the program will fail with a -904 SQL code (resource unavailable, with a code indicating that the unavailable resource is EDM pool space). Such failures due to pool-full conditions are also recorded in the EDM Pool section of a DB2 monitor statistics report or display. These failures tend to be rare, as there are usually enough free (empty) pages in the pool to accommodate new package load requests, and even when free pages are lacking there are typically occupied pages that can be stolen for the new load because the package sections in the occupied pages are associated with packages that were acquired for threads but which were subsequently released. This is where RELEASE(DEALLOCATE) can make a difference, because that bind specification indicates that the package, once acquired for execution via a thread, is not to be released until the thread is deallocated (as opposed to being released at a commit point). If a thread persists for a long time, packages executed via the thread and bound with RELEASE(DEALLOCATE) will be considered by DB2 to be in-use for a long time, and the EDM pool pages occupied by those packages will be non-stealable for a long time. That can lead to a pool-full situation if space in the EDM pool becomes tight.
So, how is it that an organization using RELEASE(DEALLOCATE) can experience a rash of EDM pool-full-related program failures seemingly "out of the blue," only to have the problem go away by itself a short time later? Here's how: RELEASE(DEALLOCATE) works just like RELEASE(COMMIT) when a thread is deallocated at commit time. That's what will happen to a non-protected CICS-DB2 thread if it is not reused, and such a thread will not be reused unless a transaction associated with the same DB2ENTRY CICS resource as the about-to-commit (and complete) transaction is queued up and waiting for a thread. If there are enough threads defined for the DB2ENTRY resource, or if there are enough pool threads and the resource definition indicates that a transaction should overflow to the pool if an entry thread is not available, transactions won't queue waiting for threads, and non-protected threads won't be reused (they'll instead be deallocated, freeing EDM pool space occupied by packages bound with RELEASE(DEALLOCATE) and executed by way of the thread. Folks usually have enough threads defined to prevent transaction queuing, but over time, if the transaction arrival rate increases and the CICS-DB2 thread specifications aren't updated, you could reach a point at which thread re-use suddenly jumps during a peak processing period, and if the re-used (and therefore persistent) CICS-DB2 threads use packages bound with RELEASE(DEALLOCATE), the EDM pool-full situation described above (brought on by a decline in the number of stealable EDM pool pages) can occur.
If this has happened at your site, you have several options available for eliminating the problem:
- You could increase the size of your EDM pool. With DB2 V8 (and beyond) 64-bit addressing, DB2 storage pools can be a lot larger than they used to be. EDM pools in the range of 200 MB to 400 MB (not counting space used for dynamic statement caching) are no big deal, as far as I'm concerned. DB2 for z/OS V9 makes even more space available for package sections by moving parts of the sections above the 2 GB "bar" in virtual storage (you can read about this in the IBM "red book" titled "DB2 9 for z/OS Performance Topics").
- You could increase the number of CICS entry and/or pool threads to cut down on thread reuse. A DB2 monitor accounting report or online display of thread detail information will provide you with thread re-use information.
- You could bind packages with RELEASE(COMMIT) instead of RELEASE(DEALLOCATE).
Some folks would conclude that a few extra percentage points of CPU consumption is an acceptable trade for an easily-administered program bind procedure, and they'd go with RELEASE(COMMIT) for everything. Others, in very high-volume environments in which CPU consumption gets a lot of attention, would decide to go for optimal efficiency via RELEASE(DEALLOCATE). Even these folks, however, should use RELEASE(DEALLOCATE) only on a selective basis - I would NOT recommend binding all programs with that specification. The best candidates would be high-use programs executed via persistent threads (again, meaning threads that persist across commits).
Shout-out: I've had many a discussion about RELEASE(DEALLOCATE) versus RELEASE(COMMIT) with my friend Anthony Ciabattoni, lead DB2 for z/OS systems programmer at CheckFree (part of Fiserv Corporation). Anthony will be sharing some of his experience with DB2 data sharing in an IBM Webcast to be broadcast on February 24. Should be a good one, in no small part because Anthony is as energetic as he is knowledgeable. You can get more information at this url: http://www-01.ibm.com/software/os/systemz/webcast/feb24/.
posted by Robert Catterall at
6:49 AM
2 Comments:
I really enjoyed reading your blog. I'm signed up for the webcast and am looking forward to it.
Thanks for the positive feedback regarding the blog, Troy. Anthony's Webcast should be entertaining as well as informative. He has a very upbeat personality.
Post a Comment
Subscribe to Post Comments [Atom]
<< Home