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.