DB2 & IMS

Performance: Why I Want DB2 9 for z/OS

2 Pages

Each new version of DB2 for z/OS offers many new functions, features and options to improve all aspects of DB2 for z/OS, the leading Database Management System (DBMS) in the industry. The new release of DB2 for z/OS is attractive because it fulfills the top requirement of many sites: improved performance.

DB2 9 for z/OS, released on March 6, has many significant features for those seeking to improve their overall system performance and availability. This article provides a brief description of some of the DB2 9 features you can leverage to improve system performance. Since the new release is so feature rich, this article only highlights just a few of the performance improvements your developers can look forward to leveraging in your environment.

New Tablespace Types

The new version of DB2 provides a new tablespace definition type called the universal tablespace. This new universal tablespace type combines the advantages and features of the segmented tablespaces with the various capabilities of partitioned tablespaces. Each partition has segmented space map pages.

The new universal tablespace also lets the administrator leverage all the ADD and ROTATE capabilities of partitioned tablespaces with the convenience of segmented space map pages, which are great for mass inserts and deletes. With the ability to add partitions on the fly, this tablespace can be used for almost any database table.

One variation of the new tablespace is the Partition-By-Growth (PBG) tablespace. PBG leverages the new universal tablespace with segmented partitions, but lets the user define the tablespace with no partitioning key. The tablespace starts out as a single tablespace partition and expands to multiple partitions as it grows. This tablespace uses the MAXPARTITIONS keyword instead of the NUMPARTS/ KEY RANGE definitions to determine the number of partitions to dynamically add when needed, providing the ability to grow the tablespace naturally. This is now the default tablespace in DB2.

Using the maximum number of partitions allowed in a PBG tablespace, the table can hold up to 128TB of data. The tablespace page size default is 4KB, leading to a tablespace size of 16TB. Additional capacity is achieved by supporting combinations of larger data set sizes (up to 64GB and page sizes up to 32KB). Figure 1 shows the size combinations available in PBG large tablespaces.

Tables have an additional APPEND option that adds inserted rows at the end of the table, and doesn’t search for space. It’s much like the current operation of the LOAD RESUME utility. This lets DB2 insert faster in high-volume situations. This functionality is great for adding data, but bad for clustering, which could be bad for performance. A simple reorganization of the partition can bring the APPENDed data into clustering sequence quickly and at your convenience.

Improved Table Management and Performance

2 Pages