Beyond the Rebind

Realize the benefits of other DB2 11 for z/OS enhancements through administration intervention

Recently, in the previous article, “The Power’s in the Rebind,” I focused on performance enhancements available in IBM® DB2® 11 for z/OS® data management software, particularly because of the rebind capability. Here, the focus is on some improvements that require an intervention from the database administrator (DBA), developer, or system administrator (SYSADM). As stated in the that article, how much any organization benefits from DB2 11 for z/OS improvements can vary, depending on the features used and the type of workloads run.

Generating statistics

DB2 has for some time needed better statistics. Many DBAs have noticed a growing need for increased accuracy and more in-depth RUNSTATS. The days of merely running RUNSTATS TABLE(ALL) INDEX(ALL) KEYCARD are, or at least should be, far behind. Many DB2 10 performance problems simply disappear when the appropriate statistics have been gathered. In DB2 10 the difficulty lies in detecting the missing statistics and figuring out the level of depth and detail to gather statistics.

DB2 11 for z/OS introduces the capability of the optimizer to provide feedback that reports statistics it is missing, which are needed to enhance calculating the access path. The process is very similar to the collection of real-time statistics. During prepare or bind, the optimizer detects that certain statistical information is lacking and stores this discovery in memory. At regular intervals—STATSINT—the in-memory information is asynchronously written to the SYSSTATFEEDBACK table.

Similar to the real-time statistics tables, this new feedback table can be used as input—either by vendor or house-written tooling—to adapt the RUNSTATS utilities to collect the statistics the optimizer needs. More importantly, the same type of information can be forced by doing an explain of a query or package. The optimizer then writes out this statistics information to the DSN_STAT_FEEDBACK table, which can then be used as input for generating highly accurate RUNSTAT jobs.

Writing logs

A number of DB2 11 for z/OS improvements helps ensure that logging occurs more efficiently than in previous versions and with enhanced performance. One enhancement moves the log write buffer—OUTBUFF—into 64-bit common storage. Moving the buffer there helps reduce processor usage by removing cross-address space operations during log writes. This reduction can have a significant positive impact on processor consumption of intensive update batch jobs. However, during—or preferably before—migration to DB2 11, having the SYSADM or systems programmer verify if HVCOMMON in SYS1.PARMLIB(IEASYS) can accommodate the output log buffers is important.

An additional logging improvement in DB2 11 for z/OS is extended Log Record Sequence Number (LRSN) capability. In DB2 10, when doing sequential inserts and updates in a data-sharing environment, a DBA or SYSADM may be facing LRSN spin wait because each LRSN needs to be unique. Once the migration to DB2 11 has occurred, DBAs or SYSADMs should activate extended LRSN; the LRSN is then converted to 10 bytes rather than 6 bytes. When both the bootstrap data sets (BSDSs) and the objects have been converted, LRSN spin is avoided. Depending on workload, no LRSN spin can have a significant performance impact. It does require the objects to be converted, which entails first converting the BSDS and reorganizing the required objects.

Changing data definition language

Imagine an index on a nullable column, despite the fact that most column values are NULL. As a result, during insert, update, and delete operations, the index has to be maintained and most of the time the value would be NULL. Enter the exclude NULL index; it can have a great impact on high, concurrent insert workloads—however, evidently there is a trade-off. Readers searching for COLUMN IS NULL cannot be serviced by this index.

DBAs or SYSADMs that are currently using global declared temporary tables (GDTTs) are surely aware of the overhead involved in using them. Prior to DB2 11, every insert, update, and delete operation had to be logged. This much logging can be very costly and may cause very lengthy rollback processing.

DB2 11 for z/OS in new feature mode (NFM) offers the DBAs and SYSADMs a choice to log—default behavior—or to not log changes to a GDTT. If the option to not log changes to a GDTT is selected, then the DBA or SYSADM must also specify what needs to happen at the moment of rollback: either delete the rows or preserve the rows. Both can be valid options, but the definition has to be known so that the developers can code accordingly in case of a rollback.

Another enhancement that also requires new data definition language (DDL) is the ability to preserve an amount of free space only for update operations. Previously, a DBA or SYSADM could specify PCTFREE to keep a certain percentage of free space on a page for future insert and update operations. However, a problem arises when all of the free space has already been consumed by insert operations by the time the first update occurs. If the row size should grow because of the update, the newly updated row will be stored on another page while the original pointer remains on the original page. This scenario is known as the “indirect referencing” phenomenon. It can be a very expensive process, because every time that row is needed, a second page has to be referenced. DB2 11 in NFM allows for specifying PCTFREE x FOR UPDATE y, thereby guaranteeing a percentage y to be free space reserved for update procedures only.

As always, the axiom “your mileage may vary” applies to all the new DB2 11 for z/OS performance features described here and in the previous article, “The Power’s in the Rebind.” However, they each have the potential for making client organizations very happy. The enhancements described here require some work to be done on the DBA’s or SYSADM’s end, and they go beyond a rebind.

Please share any thoughts or questions in the comments.

[followbutton username='db2kurts' count='false' lang='en' theme='light']
[followbutton username='IBMdatamag' count='false' lang='en' theme='light']