DB2 for z/OS ZPARMs that Organizations Consistently Set in a Sub-Optimal Fashion

Over the past several years, I have reviewed DB2 for z/OS systems running at quite a few client sites. Part of the analysis work I do in performing these reviews involves looking over a DB2 subsystem's DSNZPARM values (or ZPARMs, for short -- the parameters through which the set-up of a DB2 system is largely specified). I have seen that certain ZPARM parameters are very regularly set to values that are not ideal. In this blog entry I will spotlight those ZPARMs, showing how they often ARE set and how they SHOULD be set (and why). Without further ado, here they are:

• PLANMGMT -- Often set to OFF -- should be set to EXTENDED (the default), or at least BASIC. "Plan management" functionality (which, though not suggested by the name, is actually about packages) was introduced with DB2 9 for z/OS. At that time, it was one of those "not on a panel" ZPARMs (referring to the installation/migration CLIST panels), and it had a default value of OFF. Starting with DB2 10, PLANMGMT landed on a panel (DSNTIP8), and its default value changed to EXTENDED (BASIC is the other possible value). Here is why PLANMGMT should be set to EXTENDED (or BASIC -- and I'll explain the difference between these options momentarily): when that is the case, upon execution of a REBIND PACKAGE command, DB2 will retain the previous "instance" of the package. Why is that good? Because, if the previous instance of a package has been retained by DB2, that instance can very quickly and easily be put back into effect via REBIND PACKAGE with the SWITCH option specified -- very useful in the event that a REBIND results in an access path change that negatively impacts program performance. In this sense, plan management functionality (along with REBIND PACKAGE options APREUSE and APCOMPARE) is intended to "take the fear out of rebinding" (in the words of a former colleague of mine). That is important, because, starting with DB2 10, we (IBM) have been strongly encouraging folks to rebind ALL plans and packages after going to a new version of DB2 (and that should be done upon initial migration to the new version, i.e., in conversion mode if we are talking about DB2 10 or 11). Those package rebinds are critically important for realizing the performance improvements delivered by new versions of DB2, and they also promote stability because system reliability is likely to be optimized when package code -- which is, after all, executable code, being, essentially, the compiled form of static SQL statements -- is generated in the current-version DB2 environment. As for the difference between the EXTENDED and BASIC options for PLANMGMT, it's pretty simple: while both specifications will cause DB2 to retain the previous instance of a package when a REBIND PACKAGE command is executed, EXTENDED will result in DB2 also retaining a third instance of the package, that being the "original" instance -- the one that existed when REBIND was first executed for the package with plan management functionality enabled (you can periodically execute the FREE PACKAGE command for a package with PLANMGMTSCOPE(INACTIVE) to free previous and original instances of the package, to establish a new "original" instance of the package). With plan management functionality serving such a useful purpose, why is PLANMGMT regularly set to OFF? That could be due to a package storage concern that was effectively eliminated with a DB2 directory change wrought via the move to DB2 10 enabling new function mode. Lack of understanding about that change, I suspect, explains why this next ZPARM is commonly set in a sub-optimal way.

• UNIT (and UNIT2) -- Often set to TAPE (the default) -- should be set to a value that will cause DB2 to write archive log data sets to disk. Why do you want DB2 archive log data sets to be written to disk? Because, if multiple database objects have to be recovered and those recoveries will all require information from a given archive log data set, the recoveries will have to be SINGLE-THREADED if that archive log data set is on tape. Why? Because multiple jobs cannot access the same archive log data set at the same time if the data set is on tape. If the archive log data set is on disk, the aforementioned recovery jobs can be run in parallel, and the multi-object recovery operation will complete much more quickly as a result. Wouldn't the storage of archive log data sets on disk consume a whole lot of space? Yes, if that's where the archive log data sets stayed. In practice, what typically occurs is an initial write of an archive log data set to disk, followed by an automatic move, a day or two later (by way of HSM, which is more formally known as DFSMShsm), of the archive log data set to tape. In the event of a recovery operation that requires an archive log data set that has been HSM-migrated to tape, there will be a brief delay while the data set is restored to disk, and then multiple concurrently executing RECOVER jobs will be able to read from the archive log data set on disk. [Note that single-threading of RECOVER jobs is forced when archive log data sets are written to virtual tape, even though the actual media used is disk -- it's still tape from a DB2 perspective.]

• UTSORTAL -- Often set to NO -- should be set to YES (the default). When a DB2 utility such as REORG is executed, and one or more sort operations will be required, by default that sort work will be accomplished by DFSORT. You could, in the JCL of REORG (and other sort-using) utility jobs, explicitly allocate sort work data sets for DFSORT's use. A better approach would be to let DFSORT dynamically allocate required sort work data sets. An EVEN BETTER approach is to have DFSORT dynamically allocate required sort work data sets AS DIRECTED BY DB2. That latter approach depends, among other things, on the UTSORTAL parameter in ZPARM being set to YES. More information on this topic can be found in an entry I posted to this blog back in 2011 -- that information is still applicable today.

• MGEXTSZ -- Often set to NO -- should be set to YES (the default). This ZPARM pertains to secondary disk space allocation requests for DB2-managed data sets (i.e., STOGROUP-defined data sets -- and all your DB2 data sets should be DB2-managed). For such data sets, you can set a SECQTY value yourself that will be used when a data set needs to be extended, but a MUCH BETTER approach is to have DB2 manage secondary disk space allocation requests for DB2-managed data sets. That will be the case when the ZPARM parameter MGEXTSZ is set to YES, and when SECQTY is either omitted for a table space or index at CREATE time, or ALTERed, after CREATE, to a value of -1. When DB2 manages secondary disk space allocation requests, it does so with a "sliding scale" algorithm that gradually increases the requested allocation quantity from one extend operation to the next. This algorithm is virtually guaranteed to enable a data set to reach its maximum allowable size, if needs be, without running into an extend failure situation. DB2 management of secondary space allocation requests has generally been a big success at sites that have leveraged this functionality (as I noted some years ago in an entry posted to the blog I maintained while working as an independent DB2 consultant, prior to re-joining IBM in 2010). About the only time I've seen a situation in which DB2 management of secondary space allocation requests might be problematic is when disk volumes used by DB2 are super-highly utilized from a space perspective. If space on disk volumes used by DB2 is more than 90% utilized, on average, it could be that DB2 management of secondary space allocation requests might lead to extend failures for certain large data sets (caused by reaching the maximum number of volumes across which a single data set can be spread), due to very high degrees of space fragmentation on disk volumes (such high levels of volume space utilization can also preclude use of online REORG, as there might not be sufficient space for shadow data sets). Personally, I like to see space utilization of DB2 disk volumes not exceed 80%, on average. The cost "savings" achieved by utilizing more than that amount of space are, in my mind, more than offset by the reduction in operational flexibility that accompanies overly-high utilization of space on DB2 disk volumes.

• REORG_PART_SORT_NPSI -- Often set to NO -- should be set to AUTO (the default) in a DB2 11 or later environment. A few years ago, a new option was introduced concerning the processing of non-partitioned indexes (aka NPSIs) for partition-level executions of online REORG. With the old (and still available) processing option, shadow NPSIs would be built by way of a sort of the keys associated with table space partitions NOT being reorganized via the REORG job, and then entries associated with rows in partitions being REORGed would be inserted into the shadow indexes. With the new option, shadow NPSIs would be built by way of a sort of ALL keys from ALL partitions, and then entries for rows of partitions being reorganized would be updated with the new row ID (RID) values reflecting row positions in the reorganized partitions. It turns out that the new option (sort all keys, then update entries with new RID values, as needed) can save a LOT of CPU and elapsed time for some partition-level REORG jobs versus the old option (sort keys of rows in partitions not being reorganized, then insert entries for rows in partitioned being reorganized). The REORG_PART_SORT_NPSI parameter in ZPARM specifies the default value for NPSI processing for partition-level REORGs (it can be overridden for a particular REORG job), and when it is set to AUTO then DB2 will use the newer processing option (sort all, then update as needed) when DB2 estimates that doing so will result in CPU and elapsed time savings for the REORG job in question (versus using the other option: sort keys from non-affected partitions, then insert entries for partitions being REORGed). I highly recommend going with the autonomic functionality you get with AUTO.

• RRF -- Often set to DISABLE -- should be set to ENABLE (the default). With Version 9, DB2 introduced a new mode by which columns of a table's rows can be ordered (and here I am referring to physical order -- the logical order of columns in a table's rows is always determined by the order in which the columns were specified in the CREATE TABLE statement). When reordered row format (RRF) is in effect, a table's varying-length columns (if any) are all placed at the end of a row, after the fixed-length columns, and in between the fixed-length and varying-length columns is a set of offset indicators -- one for each varying-length row (the 2-byte offset indicators replace the 2-byte column-length indicators used with basic row format, and each offset indicator provides the offset at which the first byte of the corresponding varying-length row can be found). Reordered row format improves the efficiency of access to varying-length columns (an efficiency gain that increases with the number of varying-length columns in a table), and can reduce the volume of data written to the log when varying-length column values are updated. The ZPARM parameter RRF can be set to ENABLE or DISABLE. With the former value, new table spaces, and new partitions of existing partitioned table spaces, will be created with RRF in effect; furthermore, tables for which basic row format (BRF) is in effect will, by default, be converted to reordered row format when operated on by REORG or LOAD REPLACE utility jobs. Besides the aforementioned benefits of greater efficiency of access to varying-length columns and potentially reduced data volume for logging, RRF is GOING to be the primary column-ordering arrangement in a DB2 12 environment: the RRF parameter in ZPARM is gone with that version, new table spaces WILL be created with RRF in effect, and existing table spaces WILL be converted to RRF by way of REORG and LOAD REPLACE. My advice is to get ahead of the game here, and setting the ZPARM parameter RRF (in a DB2 10 or 11 environment) to ENABLE will help you in this regard.

You might want to examine ZPARM settings at your site, and see if you spot any of these (in my opinion) less-than-optimal specifications. If you do, consider making changes to help boost the efficiency, resiliency, and ease-of-administration of your DB2 systems.