MAINFRAME > Administrator > Db2

DB2 Table Space - Is it Time to Redesign?

The latest DB2 for z/OS releases provide additional table space and index design options.

The latest DB2 for z/OS releases provide additional table space and index design options.
Illustration by Leigh Wells

As a database administrator (DBA), I find it's important to review what's new in each release of DB2* and see how these new features can be used to improve the performance, availability and maintenance of the data. It's important to also understand your business and the requirements of the application when designing the physical tables and table-space storage options. The last two releases of DB2 for z/OS* have brought some nice storage-design options for DBAs.

When a DBA is creating a table, a design decision has to be made as to what type of table space the table will be stored in. In case you're unfamiliar with the term table space, IBM defines it as "a page set that is used to store the records in one or more tables." To decide what table space type to use, a DBA not only needs to know and understand the characteristics of the different types of table spaces, but also must have an understanding of the storage requirements, application availability needs and type of processing to determine the type of table space to use.

In this article, I'll review the available table space and index design options and see why people are upgrading to get the flexibility that comes with Version 8 table-controlled partitioning and where the future will take us in terms of Version 9 universal table space and XML support.

DB2 Basics

Before we get into design considerations you need to understand the different types of table spaces - simple, segmented, partitioned, large object (LOB), XML and universal. With the first few releases of DB2 your only option was to store one or more tables in a simple table space. The rows were intermixed on the pages, which could cause a performance problem because DB2 must read the data for all tables in the table space, not just the data for the table of interest. IBM greatly improved upon the limitations of the simple table space with the introduction of segmented and partitioned table spaces. IBM has highly recommended discontinuing the use of simple in favor of segmented. As of DB2 V9 the simple table space isn't available.

Segmented table spaces are usually used to store relatively small tables. The pages are divided into segments and each segment is the same size (a multiple of four up to 64), and can store as much as 64 GB of data. DB2 supports more than one table in a segmented table space, but will assign a segment to only one table so you'll never have rows from two different tables in a segment. This improved space management and data-retrieval performance because DB2 can qualify the segments needed and read into the buffer pool only the pages that are needed for the table being processed. You can read more about the characteristics of the segmented table space in the DB2 Administration Guide along with other DB2 V9.1 for z/OS books (www.ibm.com/software/data/db2/zos/v9books.html).

Partitioned table spaces are used typically to support relatively large tables to improve availability, performance and maintenance. A partitioned table space as defined by IBM is "a table space that is based on a single table and that is subdivided into partitions, each of which can be processed independently by utilities." A partitioned table space can have from 1 to 4,096 partitions and can store as much as 128 TB of data. Prior to DB2 V8, DB2 determined which partition to store the data in by a partitioning index. This method of partitioning is called "index-controlled partitioning." The high key value known as the limit key value is defined for each partition on the create index statement. This index is the clustering index so data has to be physically stored based on the order of the columns in the partitioning index. If you need to change the number of partitions or the columns defined to the partitioning index, you must drop the table space and start over.

An example of index-controlled partitioning can be seen in Figure 1. The employee table has two partitions. The first partition has a limit key value of 000110, and the second partition has a limit key of 999999. This limit key tells DB2 to store the data in this partition if it's lower than this value. With DB2 V8 you have the option of making this value inclusive. DB2 doesn't enforce the high limit key value in the last partition unless the table space has been defined as a large table space.

You can define more than one index on a partitioned table. These other indexes are called nonpartitioning indexes (NPIs). With partitioning table space, a utility job can work on part of the data while other applications concurrently access data in other partitions. Once you define an NPI you lose that partition independence. You now have an index with pointers to more than one partition. The REORG utility will process the specific partition and its partition index and then rebuild the NPIs in the BUILD2 phase. During this phase, data isn't available through these indexes. This issue goes away in V9 because the BUILD2 phase has been eliminated.

When IBM introduced the support of LOB data types to hold graphics, video and very large text strings, it introduced a LOB table space to contain this data. The table space that contains the table with the logical LOB column is called a base table space. The LOB table space is always associated with the table space that contains the logical LOB column value.

Troy L. Coleman is an IBM-certified Certified database administrator, specializing in DB2 9 for z/OS and Linux, UNIX and Windows. Troy can be reached at troy.coleman@gmail.com.


comments powered by Disqus

Advertisement

Advertisement

2019 Solutions Edition

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

An Intuitive Approach to DB2 for z/OS SQL Query Tuning

Real-world samples provide valuable lessons.

MAINFRAME > ADMINISTRATOR > DB2

A Db2 Utilities Migration Project

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