This and the previous article (z/Journal, June/July 2003) highlight the many different areas where DB2 is being enhanced to leverage the new z/OS capabilities and provide new features that take databases to the next level. This article examines the Very Large Database (VLDB) enhancements along with migration considerations for getting to DB2 UDB Server for z/OS Version 8. We also examine other enhancements, such as the new index type, Data Partition Secondary Indexes (DPSIs), statistic improvements, and new DSNZPARMS that provide numerous dynamic adjustment capabilities.
Most of the new features in DB2 Version 8 can be somewhat related to the performance, availability, and scalability considerations for VLDBs. All of the availability considerations of column redefinition and addition are beginning to be addressed through online schema evolution. Modifying a database table or index no longer requires the object to be offline. Now changes can be incorporated as needed without interrupting processing. Other availability enhancements include the ability to define or change a table’s clustering and also have it separated from the partitioning schemes. These definition and design flexibilities provide many alternative design opportunities for huge application performance improvements without the availability concerns of unloading and reloading VLDB data. Schema evolution is a huge benefit for everyone involved with the management and maintenance of any size database, but especially for VLDBs.
Additionally, the expansion to 4,096 partitions, along with the ability to add a partition at the end of a partition and rotate partitions from the beginning to the end of the partition, will help VLDBs tremendously. This flexibility, along with the new DPSI index structures, offers users the ability to run applications or maintenance against multiple partitions in parallel, which will dramatically reduce overall elapsed time.
The expansion of the memory on the z/OS platform offers a unique opportunity for VLDBs. DB2 Version 8 expansion of all its memory-related components such as buffer pools, local agent for parse trees, the RID Pool, the Sort Pool, along with better handling for compression dictionaries and the EDM Pool, allow more application processing. Expanding the memory capabilities of these components provides more flexibility to focus memory resources on VLDB issues such as buffering more data, and handling more concurrent users and more applications. As CPUs become faster and disk space becomes less expensive, efficient memory usage is the best way to address the ever-increasing I/O requirements of VLDB workloads.
Version 8 also addresses the processing of VLDB workloads through the expansion of the active and archive logs available to a system. Many VLDB enterprises were processing so many transactions that logging and retaining the archive data was a major recovery consideration. DB2 Version 8 addresses these issues by expanding the number and size of the logs available to an active DB2 system. In Version 8, the active log can now hold up to 372GB of active data and the archive logs can hold up to 40TB of data. These enlarged capabilities greatly improve the recovery prospects for high-transaction VLDB systems. These log expansions, along with the integration and improvements of z/OS 1.5 DFSMS, provide even better handling for VLDB system checkpoints, backups, and recoveries through FlashCopy facilities. Through DFSMS FlashCopy facilities, DB2 Version 8 backups and recoveries are easier to set up and execute, while the new utility options of Backup System and Restore System speak for themselves.
DPSIs AND INDEXING IMPROVEMENTS
DPSIs are another major enhancement in Version 8 that every DB2 installation will benefit from. The DPSI design improvements were developed for Version 8 in response to the many availability and performance considerations of Non-Partitioning Indexes (NPIs). Since the NPI structure references all the table entries across all partitions, it became a single point of recovery, performance, and locking consideration. Since partitioned tables are commonly some of the largest tables defined for an application, defining many NPIs on a large table has become a major liability for maintenance, concurrency, and availability issues. Also, as data growth continues, the NPIs sometimes contain the most database row entries of any single database object in an entire shop. As data growth approaches the petabyte range, DB2 continues its leadership in technology and performance as it addresses this NPI design issue with the new DPSIs.
DPSIs are extremely important because of their relationship with several of the other new features in Version 8 associated with online schema evolution. The new DPSI implementation, tablespace data clustering options, expanded number of partitions available, and the partitioning rotation options provide many flexible combinations.
Practically speaking, DPSIs are vital because they eliminate the large data considerations from recovery, load, and concurrency issues for DBAs and applications. By splitting the index entries into smaller partition parts, DPSIs provide the ability to divide and conquer the large data issues. For example, previous BUILD2 phases required by loads, reorganizations, or recoveries of NPIs are no longer needed, as DPSIs prevent a huge maintenance time outage. Also, since DPSIs are partitioned along the same scheme as the data, parallelism for all maintenance processes and application SQL is now possible, saving significant amounts of time for all activities. Concurrency is also improved because the locks are no longer associated with a single NPI data set. In a data sharing environment, DPSIs provide better granularity for locks because they spread the locks across numerous Partitioned Data Sets (PDSes).