- 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.
Very useful, short and clear details, as usual.
ReplyDeleteThank you Robert, you are my favorite DB2 performance author
Duc.
Thank you for the kind words, Duc.
DeleteRobert
Very helpful - but I have one question
ReplyDeleteWhat factors influence why amoung the 100's of trx which see a small CPU performance improvement, we always have a dozen or so trx which increase 10x or 100x in CPU cost? What factors might contribute to this pattern. Our mgmt see these and they become increasingly concerned about upgrades, even when most of the trx get better. Often after time and effort we are able to revert the package back to an older access path which performs better. It would be helpful to understand what factors we might look for before the upgrade to avoid some of these - thanks
If catalog statistics for an object or objects accessed by a transaction (e.g., a table, table space, index) are out of date or incomplete, it's possible that a rebind of the transaction's package will result in the selection by DB2 of an access path that turns out to be suboptimal from a performance perspective; so, keeping catalog statistics up-to-date can be a means of reducing these negative surprises.
DeleteThat said, you really can't guarantee that a package rebind won't result in access path changes that will negatively impact a transaction's performance. Such an occurrence is, as you've pointed out, unlikely but not impossible. For that reason, and because package rebinds are important for realizing various benefits delivered by a new version of DB2, the DB2 for z/OS development organization came up with some enhancements aimed at "taking the fear out of rebinding," as a colleague of mine put it. You should take advantage of these enhancements.
Chief among these rebind anxiety-reducing features is what's called plan management. You state that "often after time and effort we are able to revert the package back to an older access path which performs better." Getting back to a better-performing access path should require very little time and effort on your part. By default, on a DB2 10 or 11 subsystem plan management EXTENDED is in effect (check the value of the PLANMGMT parameter in ZPARM). In that case, when you rebind a package DB2 will retain the previous instance of the package. If the rebind action causes an access path change that results in degraded performance, just issue a REBIND command for the package with the SWITCH(PREVIOUS) option, and you're right back to the prior, well-performing version of the package.
Beyond plan management, there are new rebind options such as APCOMPARE (DB2 will let you know if a rebind causes an access path change) and APREUSE (DB2 will try to reuse existing access paths when rebinding a package) that you can utilize to mitigate risk of a performance problem caused by an access path change.
So, rebind packages when you go to a new version of DB2, but have plan management EXTENDED (or at least BASIC) in effect so that you can do a quick REBIND with SWITCH for a package if a negative performance surprise pops up, and consider the use of options such as APCOMPARE or APREUSE to batter manage access path generation.
Robert
The change in access path will not change by the use of release commit vs release deallocate. DB2 will calculate a "bad" access path when the actual table data distribution is no longer matched to stats or not enough stats are collected to reflect that data. There are many possible sources -- is it a work table that should have the volatile attribute ? 3+ columns in an index without intermediate distributions? Skewed data? table now 5x size vs stats saved?
DeleteThat is correct. Access path selection does not depend on the RELEASE specification of a BIND or REBIND operation. Access path changes can occur when packages are rebound, and one reason for rebinding packages is to go from RELEASE(COMMIT) to RELEASE(DEALLOCATE).
DeleteRobert this is good stuff. We are in the process of implementing High performance DBATs. IBM warns that PKGREL(COMMIT) be issued prior to running DDL and utilities specifically reorgs. We run share level change reorgs but still we will modify PKGREL(COMMIT) prior to our DDL and reorg window starts. Should we be concerned about running Runstats and image copies share level change with PKGREL(BINDOPT) and some packages bound with release(deallocate). Please advise. Thanks. Ranga.
ReplyDeleteSorry about the delay in responding.
DeletePackages bound with RELEASE(DEALLOCATE) and executed by way of persistent threads (meaning, threads that persist through commits, such as high-performance DBATs, CICS-DB2 protected entry threads, batch job threads, etc.) can block some DDL, bind/rebind, and utility operations. Here is why: a package cannot be invalidated or replaced while it is in-use, and a package bound with RELEASE(DEALLOCATE) and allocated to a persistent thread is considered to be continuously in-use until the thread is deallocated (there was a change here with DB2 11 - I'll cover that in a moment). A bind or rebind of the package will fail because the in-use package cannot be rebound or replaced. An ALTER that would invalidate the package will fail because the in-use package cannot be invalidated. An online REORG that would materialize a pending DDL change will not complete successfully because such a REORG has to invalidate packages dependent on the object being REORG'ed, and, as noted, an in-use package cannot be invalidated.
An online REORG that is NOT executed to materialize a pending DDL change should not require invalidation pf packages, and thus should not be affected by RELEASE(DEALLOCATE) packages allocated to persistent threads such as high-performance DBATs. Similarly, image copy and RUNSTATS jobs should not be affected by RELEASE(DEALLOCATE) packages allocated to persistent threads, because image copy an RUNSTATS jobs should not require package invalidation. Even an operation that will result in rebinding or replacing or invalidating a package will not be affected by package ABC being bound with RELEASE(DEALLOCATE) and being allocated to a persistent thread, if the package affected by the bind/rebind, DDL, or pending DDL-materializing online REORG is package XYZ versus package ABC (and if package XYZ is not itself bound with RELEASE(DEALLOCATE) and allocated to a persistent thread.
With DB2 11, if a bind/rebind or DDL or pending DDL-materializing online REORG operation would be blocked by a RELEASE(DEALLOCATE) package allocated to a LOCAL persistent thread (i.e., not a DBAT), DB2 will, automatically, temporarily change the package's behavior to RELEASE(COMMIT) and will drain use of the package so the use count will go to zero, and that will un-block the bind/rebind or DDL or pending DDL-materializing online REORG operation that had been held up. For high-performance DBATs, one still has to issue the -MODIFY DDF PKGREL(COMMIT) command in a DB2 11 environment if a bind/rebind or DDL or pending DDL-materializing online REORG would be blocked by a RELEASE(DEALLOCATE) package allocated to a high-performance DBAT, but the command should un-block things faster than would be the case in a DB2 10 system.
Robert
Hello Robert,
ReplyDeleteVery informative as always.
I'm thinking of leveraging this at my shop. We're on DB2 10 NFM and CICS 4.2.
I've got a DB2 package that could be regarded as quite 'simple' - basically authenticates an application user with a single SQL against a User table. There are about 70+ transactions executing this package. This gets execute 2000+ times a minute - An ideal candidate for changing the RELEASE parm to DEALLOCATE, I would think.
Obviously to leverage full benefit of RELEASE-DEALLOCATE'ing, all the 70+ transactions will need to be altered for its THREADLIMIT and PROTECTNUM parms for their DB2E in all AORs.
Is there any known issues that I may consider from a CICS perspective before we make the CICS change?
Thanks in advance.
Cheers,
Vibin
Sorry about the delay in responding. I have some considerations pertaining to protected entry threads in this blog entry: http://robertsdb2blog.blogspot.com/2011/12/db2-for-zos-driving-thread-reuse.html
DeleteCheck that blog entry out, and in particular pay attention to the TCBLIMIT consideration.
Of course you don't have to change all 70+ DB2ENTRY resources (if each of the transactions you mentioned has its own DB2ENTRY) to start using protected threads to drive thread reuse higher. I have to believe that some of these 70+ transactions are executed more frequently than others. Start with those that are executed most frequently. Also, keep in mind that you have to be on DB2 11, in NFM, I believe, before you get automatic RELEASE(DEALLOCATE) "break-in," described in this blog entry: http://robertsdb2blog.blogspot.com/2014/02/db2-11-for-zos-bind-concurrency-even.html
Prior to DB2 11 NFM, you'll have to determine whether use of protected threads and RELEASE(DEALLOCATE) packages will get in the way of BIND/REBIND, ALTER, and/or online REORGs (the latter executed to materialize pending DDL changes). This won't be a problem if your transaction volume goes down enough at night or on weekends to allow the protected threads to be deallocated. If that doesn't happen - if transaction volume is such that the protected threads could be around all the time, RELEASE(DEALLOCATE) packages executed via those threads could impede some database administration tasks in a DB2 10 for z/OS system.
Robert
Hello,
ReplyDeleteI'm trying to improve performance by using RELEASE(DEALLOCATE) in batch programs. We have a central routine for COMMIT / RESTART, which issues all COMMITs itself. If I want to do the RELEASE parameter change at the package level, which package should I target? the one doing the most SQL (main program for instance) or the one doing the COMMITs?
Thanks.
It is not an either/or matter. The CPU benefit of RELEASE(DEALLOCATE) is greatest (assuming a persistent Db2 thread, such as a batch job's thread) for a package that is 1) executed frequently during the time that a thread through which the package is executed exists and 2) has a relatively low in-Db2 CPU time. If the COMMIT-issuing routine meets those criteria, binding that routine's Db2 package with RELEASE(DEALLOCATE) would be a good move, performance-wise. The same would be true for a SQL-issuing program, other than the commit routine, associated with the batch job: if it is executed frequently and is in and out of Db2 quickly, RELEASE(DEALLOCATE) can be an appropriate BIND specification.
DeleteNote that amount of SQL issued is not the point. If a program issues many SQL statements but is executed only once (i.e., within just one commit scope) by the batch job, RELEASE(DEALLOCATE) will not make a performance difference. Even if that program is executed frequently, if it has a relatively high in-Db2 CPU time per execution (e.g., more than a second), RELEASE(DEALLOCATE) may have little impact because the proportional cost of releasing (from the thread) and reacquiring (for the thread) the package sections and associated table space locks will be small.
Robert