New DB2 9 for z/OS Utilities

Within the IBM Z Summit road show that was recently in Washington DC, the DB2 presentations highlighted a number of DB2 utilities improvements. All of these DB2 utilities improvements are integrated into the new DB2 10 but there were many that can be incorporated into DB2 9 and some even into DB2 8 for z/OS.

One of the biggest DB2 performance improvements has come for the SORTBLD phase for indexes. Since many DB2 indexes have small SECQTY allocations, the APAR PK60956 (V8 & V9) helps DB2 with the serialization and their possible write delays. This APAR can speed up some LOAD, REORG, REBUILD, and RECOVER processes by as much as 20 times with indexes that have small SECQTY allocations.

APAR PK61759: When applied to V8 and V9, APAR PK61759 helps within the DFSORT phase of the LOAD and REORG facilities to reduce CPU by 10% within the RELOAD phase of these utilities.

Another APAR, PK74993 for DB2 9, improves the DB2 performance of the COPY utility. This improves the efficiencies of handling small data sets and can result in a 20% elapsed time improvement for jobs that copy a large number of small datasets to tape.

Also talked about were a number of other APARs that will help within your utility processes. All of them help DB2 performance for their unique situations. They were:

• APAR PK63324 & PK63325 for DB2 9 helps by copying the compression dictionary instead of recreating it with the new REORG LOAD COPYDICTIONARY keywords.

• When using the BACKUP SYSTEM or disk mirroring, APAR PK41711 for DB2 9 runs a data consistency checks without impacting overall DB2 performance.

• APAR PK80304 for DB2 V9 helps avoid the check pending status after a PIT recovery of a referential group. This can save and avoid enormous amount of time verifying all your RI relationship objects especially for SAP and PeopleSoft database applications.

• APAR PK45916 for DB2 version 8 and PK41899 for DB2 Version 9 provide greater utility parallelism with SORTNUM elimination. This is a big DB2 performance boost for utility sort processing.

• For those who use LOAD/UNLOAD LOBs, APAR PK75216 improves DB2 performance dramatically when using PDSs by as much as 56% for UNLOADs and 93% for LOADs.

• For UNLOAD processes from multi-table table spaces, APAR PK77313 for DB2 V8 and V9 a uses the database DBD instead of a DB2 catalog lookup. This can especially improve performance for large databases for software packages.

Of course, there may be a number of considerations and some of these may be older APARs, so research if your system needs them before putting them on your system. Also check out the ZSummit Roadshow when it comes to your town.

_____________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.