You are currently on IBM Systems Media’s archival website. Click here to view our new website.

MAINFRAME > Tips & Techniques > System Tuning

DB2 Utilities Suite Improves Efficiency and Performance

As users perform capacity planning for migration to IBM DB2 11 for z/OS and its companion DB2 Utilities Suite, it is essential to learn about the enhancements that are available. By taking advantage of the capabilities, users can further minimize downtime, maximize efficiency and improve engine and application performance while ensuring the highest degree of data integrity. With proper usage of the latest features in DB2 11 for z/OS Utilities Suite, it can further relief users' burden on routine data maintenance while observing better performance and availability.

Providing Support and Improvements

One of the primary strategies for DB2 Utilities Suite is to fully support all old and new core functions in the current release of IBM DB2 for z/OS from the time at GA, and users can expect the same when migrating to DB2 11 for z/OS.

On top of that, the DB2 Utilities Suite provides numerous improvements in the areas of better availability, finer usability, improved performance with CPU and resource consumption reduction and improved elapsed time via higher degree of subtasking parallelism. It also removes constraints and scalability limitations to further simplify data management.

One major new function introduced in DB2 11 for z/OS, which has significant impact across all utilities, is the support for 10-byte log relative byte address (RBA) and log record sequence number (LRSN). This feature requires changes to the page format for both data and index page sets, which also affects the metadata definition of certain catalog and directory objects as well as the format of the mapping table used by the DB2 Online REORG utility.

The conversion from basic page format defined with the existing 6-byte log RBA and LRSN, to the extended page format defined with the 10-byte log RBA and LRSN in DB2 11 new-function mode is enabled via the DB2 LOAD, REORG and REBUILD INDEX utilities. With the focus on availability, users can convert from basic format to extended format page set non-disruptively through the use of the Online REORG utility as part of their existing scheduled workload.

In the DB2 Utilities Suite, users can experience higher degree of utility subtasking parallelism, which generally results in further elapsed time improvement. In the other extreme, users can restrict the degree of parallelism via the PARALLEL keyword in many utilities, by limiting the system resource consumption by utility execution.

For users who want to take advantage of the improved REORG parallelism but cannot afford the resource requirements to reorganize many data partitions at once, the REORG utility on a LISTDEF has been enhanced with a new LISTPARTS option for users to determine the optimal number of partitions to be processed in a single REORG execution. For the LOAD utility, subtask parallelism is now possible on a single SYSREC input, which helps improve ease of use as well as achieving faster processing time. In addition, the CROSS LOADER performance via the INCURSOR keyword of LOAD has also been improved via the FETCH CONTINUE exploitation.

Enhanced Availability

The Online REORG utility is further enhanced to provide better availability in the DB2 Utilities Suite. The improved drain break-in process with easier drain acquisition helps avoid costly failure, while the total outage duration is further reduced by the code restructuring of the SWITCH phase, which minimizes application impacts. Data rebalancing via the REBALANCE option has also been enhanced with SHRLEVEL CHANGE processing. Overall, the REORG utility has been enhanced for far better availability compared to the previous release.

Further changes have been made to the REORG utility to further simplify data management and improve usability. The default setting for the REORG utility has been changed to match best practices—in particular, DRAIN ALL is now the default setting instead of DRAIN WRITERS, and the default setting for DISCARD and UNLOAD EXTERNAL processing is changed to NOPAD YES. In addition, Online REORG can now automate the creation and deletion for the mapping table, so that users no longer have to manage the mapping table outside of REORG execution.

For partition-by-growth table space, REORG allows for better management through the physical deletion of trailing empty partitions. For improving data set management, the REORG utility has been enhanced to create partition-level image copies, which also enables faster partition level recovery by the RECOVER utility. The SORTDATA NO option can now be specified with SHRLEVEL CHANGE option. New option of RECLUSTER YES/NO on SORTDATA NO allows users to execute the REORG utility without the high cost of data reclustering when clustering is unimportant or when the data is already highly clustered.

Capturing More Information

The RUNSTATS utility has been enhanced to gather default statistics profile when no profile exists for user objects. It can now also receive inputs from the DB2 optimizer on missing statistics, which eliminates the users' burden to determine what statistics to gather. At the same time, users might not need to execute the RUNSTATS utility following LOAD, REORG and REBUILD INDEX execution since the inline statistics function can now collect the same COLGROUP and HISTOGRAM statistics as the RUNSTATS utility. There is also additional zIIP offload for distribution statistics collection and inline statistics execution.

MRU bufferpool management has been extended to various utilities, such as UNLOAD, REORG TABLESPACE, RUNSTATS TABLESPACE, RUNSTATS INDEX, REBUILD INDEX, CHECK INDEX and CHECK DATA, which further lessens the utility impact on concurrent applications. In the backup and recovery area, system cloning has been improved through new volume catalog name translation for the RESTORE SYSTEM utility. Many point-in-time recovery restrictions are also lifted in DB2 Utilities Suite. Users now can recover a Lob table space, a XML table space, and a partition-by-range universal table space—with or without auxiliary objects associated with it—to a point in time that is prior to a REORG utility that materialized supported table space attribute pending definition changes. This includes the support when immediate alters have occurred since the materializing REORG operation.

To provide more information and greater transparency, the DISPLAY UTILITY command has been improved to remove serialization between the display command of -DIS UTIL and the termination command of -TERM UTIL. The DISPLAY output also now includes the job name as well as the start time stamp of the associated utility.

An Essential Tool

The DB2 Utilities Suite is essential for the exploitation of the major DB2 11 for z/OS enhancements, and has full support of DB2 11 for z/OS from day one of the GA. Fully utilizing the new features delivered in the DB2 Utilities Suite will help users to not only get the most value out of DB2 11 for z/OS, but also take full advantage of all the capabilities available.

Szu-Huey Chuang is an Advisory Software Engineer in the IBM DB2 for z/OS organization at the Silicon Valley Laboratory.

Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.


CICS TS Performance and Tuning: A Rich Tradition


DB2 Utilities Suite Improves Efficiency and Performance


Establishing an Infrastructure for Tuning a Distributed Network

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
Mainframe News Sign Up Today! Past News Letters