How Long Has it Been Since You Last Checked Out the Performance of IBM's DB2 for z/OS Utilities?

Here is an interesting situation that exists at a number of DB2 for z/OS sites: an organization is licensed for the IBM DB2 Utilities Suite for z/OS, but opts to use utilities from another vendor for various DB2 database administration tasks (common examples are data load, table space or index reorganization, and generation of catalog statistics used for query access path optimization). Why do companies pay twice (once to IBM and once to another software vendor) for some of their DB2 utilities? An oft-cited reason is performance, as in, "We run vendor XYZ's DB2 data load utility because it is more CPU-efficient than IBM's LOAD utility." If this is the case at your shop, I have a question for you: when was the last time that you actually measured the performance of the IBM DB2 utilities in your DB2 for z/OS environment? If it was before DB2 9, you ought to revisit this issue -- doing so could provide your employer with an opportunity for significant savings in the area of mainframe software expenditure.

DB2 9 delivered major advancements with respect to the CPU efficiency of the IBM DB2 utilities. Tests of the performance of the DB2 9 utilities versus their DB2 V8 counterparts showed the following:

• For LOAD: CPU savings of 5-30%
• For LOAD of a partition of a partitioned table space: CPU savings of 35%
• For LOAD REPLACE of a partition of a table space with non-partitioned indexes, using a dummy input data set (a technique regularly used to quickly "empty out" a partition): CPU savings of up to 70%
• For RUNSTATS INDEX: CPU savings of 30-50%
• For REORG INDEX: CPU savings of 40-50%
• For REORG TABLESPACE and REBUILD INDEX: CPU savings of 5-20%
• For CHECK INDEX: CPU savings of 20-60%

Similar gains were seen for utility job elapsed times. Detailed information on a number of performance tests can be found in chapter 6 of the IBM "red book" titled, "DB2 9 for z/OS Performance Topics."

How were these CPU efficiency and run time improvements, available in DB2 9 conversion mode, achieved? There are three primary factors:

• A block-level interface to the DB2 index manager. The most significant CPU and elapsed time reductions are associated with utilities that process index keys. Whereas previously a call to the index manager was necessary for each key involved in a utility execution, with DB2 9 (and 10) blocks of keys can be passed with an index manager call. The resulting reduction in required index manager calls reduced CPU overhead for index-intensive utilities.
• More efficient generation of index keys. This comes into play especially for non-padded indexes with varying-length keys.
• Exploitation of shared private storage. z/OS 1.7 provided a new type of virtual storage resource known as shared private storage. With the availability of this resource (configured via the HVSHARE parameter of the IEASYSxx member of PARMLIB), address spaces can register to use a Virtual Storage Object (VSO) that is created just for those address spaces (the VSO is thus part of those address spaces' virtual storage, but not part of other address spaces' virtual storage -- this in contrast to ECSA, a shared resource that is part of every address space). A DB2 9 (or 10) utility address space can use a VSO (which is located above the 2 GB "bar" in virtual storage) to exchange data rows with the DB2 database services address space (aka DBM1) in a way that does not require the use of z/OS cross-memory services. That, in turn, reduces the CPU cost of utility execution.

While the big splash in terms of IBM DB2 utility performance was made by DB2 9, DB2 10 added some utility performance enhancements of its own, to wit (and you can get the details in chapter 9 of the IBM red book, "DB2 10 for z/OS Performance Topics"):

• COPY utility exploitation of FlashCopy functionality. DB2 10's COPY utility can utilize FlashCopy to make near-instantaneous (from a data availability perspective) backups of individual database objects (this capability is also available for inline image copies generated through the execution of other utilities, such as LOAD and REORG). The object-level, DB2-directed FlashCopy backup functionality provided with DB2 10 builds on the volume-level FlashCopy exploitation delivered via the BACKUP SYSTEM utility introduced with DB2 V8 (and significantly enhanced with DB2 9). Object-level backups created using FlashCopy can reduce host CPU consumption, particularly when used for larger objects.
• zIIP exploitation for RUNSTATS. A major portion of the processing done by a RUNSTATS execution in a DB2 10 system can be offloaded to zIIP engines (the percentage of zIIP offload will vary based on the nature of the work done by RUNSTATS). As just about everyone knows by now, zIIP MIPS are less expensive than general-purpose-engine MIPS.
• Page-level versus row-level RUNSTATS sampling. People commonly specify, on a RUNSTATS utility control statement, a sampling percentage -- this to reduce the CPU consumption of RUNSTATS execution. Basically, this told RUNSTATS to look at a percentage of rows in the table -- versus all rows -- in generating statistics for the catalog. The thing is, in looking at a relatively low percentage of a table space's rows (25% is the default SAMPLE value), RUNSTATS might have to read a large percentage of a table space's pages. The new sampling capability, specified via the TABLESAMPLE SYSTEM option, can result in RUNSTATS examining significantly fewer of a table space's pages during execution, thereby reducing CPU time for an execution of the utility.
• Online REORG use of list prefetch for disorganized indexes. When a DB2 10 online REORG job reorganizes an index that is disorganized (and this is particularly applicable to a REORG INDEX job, or to a partition-level REORG TABLESPACE job when the associated table has non-partitioned indexes), it can use list prefetch to efficiently access the index leaf pages in logical order when unloading the index to the shadow data set. This technique avoids the large number of synchronous index pages reads that might otherwise be required for the index unload, with associated CPU and elapsed time benefits.
• Exploitation of data buffering enhancements for BSAM files. By utilizing more buffers for BSAM data and page-fixing these buffers (exploiting enhancements delivered in z/OS 1.9 and 1.10), DB2 10 utilities using BSAM can run faster (COPY and UNLOAD) and use less CPU time (COPY, UNLOAD, LOAD, and RECOVER) than in previous-release DB2 systems.
• Variable-blocked spanned record support for LOAD and UNLOAD. I blogged about this a few weeks ago. Big-time performance improvement for LOAD and UNLOAD of tables containing LOB (large object) data.
• LOAD and UNLOAD of data in internal format. By eliminating column-level processing, this option (retrofit to DB2 9 via the fix for APAR PM19584) can substantially reduce CPU and elapsed time for some LOAD and UNLOAD jobs.
• PRESORTED option for LOAD. This new option (also retrofit to DB2 9 via the aforementioned APAR PM19584) eliminates LOAD-related sort of input data when that data has already been sorted in clustering key sequence prior to utility execution. Bypassing that sort operation can result in reduced CPU and elapsed time for LOAD utility execution.

On top of all this, the IBM DB2 utilities, which by default use DFSORT for sort processing, benefit from performance enhancements delivered by the DFSORT development team. An example is the performance boost -- especially as it pertains to CPU consumption -- provided by the fix for DFSORT APAR  PM18196.

So, having laid out all these recent IBM DB2 utility performance benefits, I have two questions for you:

1. How fast, and how CPU-efficient, do your DB2 utility operations need to be, and can IBM's DB2 utilities meet those requirements? As noted in the opening paragraph of this blog entry, if you haven't put IBM's DB2 utilities to the test in a DB2 9 or DB2 10 environment, and if performance has been the reason for your organization's use of other vendors' DB2 utility products, it's time to give the IBM utilities another look. You could end up helping to reduce your company's mainframe software costs (especially if your organization is already licensed for IBM's DB2 Utilities Suite for z/OS, which is often the case).
2. If IBM's DB2 utilities, even in a DB2 9 or DB2 10 environment, don't quite clear your performance bar, would they if you also had the IBM DB2 Sort product? I blogged about DB2 Sort a couple of weeks ago. DB2 Sort can take the performance of the IBM DB2 utilities to another level, and the added cost of DB2 Sort could still mean software cost savings for your organization in light of what your company may be paying for another vendor's DB2 utility products.

Let IBM's DB2 utilities perform for your company. Could be a good move on your part.

Faster, More Efficient, More Resilient DB2 Utility Sorting with DB2 Sort

If you're a mainframe DB2 person, there's a good chance that you've heard of an IBM software offering called DB2 Sort. If your knowledge of DB2 Sort doesn't extend much  beyond "I've heard of it," you may have this vague idea that the product speeds up IBM DB2 for z/OS utility sort processing. You'd be on target there, but utility speed-up is only part of the DB2 Sort story. On top of that, your thinking as to how DB2 Sort accelerates DB2 utility execution may be off the mark. With this blog entry I want to clear up a few misconceptions about DB2 Sort that I think exist to some extent within the DB2 for z/OS user community. Additionally, I'll review recent developments with respect to DB2 Sort in particular, and IBM DB2 utility performance in general.

On to clarifications.

Misconception #1: DB2 Sort reduces utility elapsed and CPU times by optimizing DB2's use of DFSORT. You probably know that, starting with Version 8, DB2 for z/OS exclusively uses IBM's DFSORT as the base for accomplishing utility-related sorts. Over time, DB2's use of DFSORT has been enhanced, but DB2 Sort is not part of that story. Instead, it is an alternative sorting tool for DB2 -- something that DB2 can call instead of DFSORT to execute sorts as needed for utility processing (the DB2 utilities that leverage DB2 Sort are the most sort-intensive in the DB2 Utilities Suite: REORG, LOAD, REBUILD INDEX, RUNSTATS, and CHECK DATA/INDEX/LOB). While DFSORT is an outstanding general-purpose sort product, DB2 Sort is truly specialized software -- highly tuned to the unique requirements of DB2 utility sort operations. When an organization's demands with regard to DB2 utility performance present a challenging target, DB2 Sort can provide the means of clearing the bar.

A little backstory: not too long ago, IBM decided to team with a long-established leader in sort technology to develop a product that would conform especially closely to the particular characteristics of sort processing in a DB2 utility context. Developers from this partner company worked with IBM DB2 utility developers to turn concept into reality, and DB2 Sort is the result of that collaboration -- a collaboration, by the way, that is ongoing.

Misconception #2: The efficiency impact of DB2 Sort is strictly a CPU thing. DB2 Sort does indeed have a positive impact on DB2 utility CPU consumption, but hardware resource utilization optimization goes beyond that effect. Take disk space, for example: having enough of this for sort work is important if a DB2 utility is to perform well (and indeed, if it is to run to completion), but as disk vendors don't give their wares away you'd prefer not to have to over-allocate space to get the results you want. DB2 Sort has an advanced dynamic allocation feature that delivers a "space on demand" capability, providing a right-sized disk resource to get a utility going, and compensating -- if needs be -- for extra requirements by allocating additional space as needed to keep utility processing on track.

Then there's system memory. When DB2 Sort is performing work for a utility, it is aware not only of what it's doing for that particular utility, but of what other concurrently executing utilities are doing -- and are planning on doing -- in terms of sort processing. DB2 Sort will balance sort-related utilization of system memory across batch address spaces to help avoid (for example) excessive paging activity that could otherwise negatively impact the performance of DB2 utilities (and application programs).

And while I'm on the topic of hardware utilization optimization, I'll remind you that boosting CPU efficiency is not just a matter of reducing overall CPU utilization (though DB2 Sort does that) -- it's also about shifting work from general-purpose mainframe processors to less-expensive zIIP engines. Use DB2 Sort with your DB2 utilities, and you'll see a greater degree of work-offload to zIIPs (assuming that your system has zIIP engines).

Misconception #3: DB2 Sort is just about DB2 utility performance. You want your DB2 utilities to finish faster, but you also want them to finish, period. DB2 Sort can boost the resiliency of DB2 utilities, allowing more utility jobs to run to completion. One way in which DB2 Sort delivers here is through retries of dynamic disk space allocation actions: if an allocation request fails, DB2 sort can wait a while (a couple of minutes, for example) and then retry the allocation action. If the initial allocation request failed due to a temporary tie-up of resources, the retried allocation could succeed. When more DB2 utility jobs complete successfully, the organization benefits, and DBAs on "pager duty" get more restful nights.

Recent developments on the DB2 Sort front: Over time, the list of DB2 tools that can utilize DB2 Sort for improved performance has expanded beyond the IBM DB2 Utilities Suite. Among the latest additions are DB2 High Performance Unload 4.1 (with the fix for APAR PM41087), the DB2 Utilities Enhancements Tool 2.2 (specifically, the LOAD presort feature), and DB2 Log Analysis Tool 3.3. What tools will be next in gaining DB2 Sort exploitability? Stay tuned.

DB2 Sort adds speed and CPU savings to DB2 utilities that were already getting faster and more efficient. DB2 9 for z/OS provided big-time performance improvements for a number of DB2 utilities, especially those that process index keys: CPU consumption for some RUNSTATS INDEX, REORG INDEX, and CHECK INDEX jobs could be 50% less in a DB2 9 versus a DB2 Version 8 environment. Other utilities such as LOAD, RECOVER INDEX, and REBUILD INDEX can also execute with substantially better CPU efficiency in a DB2 9 system (organizations going from DB2 V8 directly to DB2 10 will see these utility performance benefits in the DB2 10 environment). Considering these performance gains along with the boost that DB2 Sort can deliver might get you to thinking about some things.

Have you looked at the IBM DB2 utilities lately? You ought to. While you're at it, check out DB2 Sort. Could be a nice fit for your organization.