July 16, 2007

Using Universal Table Space

I've been traveling to DB2 regional user groups talking about DB2 partitioning and how you may want to redesign your legacy applications to take advantage of some cool new features in V8 and in V9. In these presentations, I touch on universal table space (UTS).

If you haven’t noticed, the term "universal"--as in DB2 Universal Database for z/OS Version 8--is no longer used in the product name. The new name for DB2 is DB2 Version 9.1 for z/OS. IBM hasn't eliminated the use of the term "universal." It lives on with the introduction of a new UTS storage option. A UTS is a table space that is both segmented and partitioned. It can be defined as either partitioned-by-growth or range-partitioned.

Have you ever reached the maximum size of a segmented table space and wish you would have made it partitioned because you have reached the maximum size of 64 GB? Well now you can define a segmented table space that will grow and additional segmented partitions will be added when the first partition is full. You control the number of additional partitions with the MAXPARTITIONS clause on the CREATE or ALTER table space. The MAXPARTITIONS can be from 1 to 4096, depending on DSSIZE and the page size.

With range-partitioned UTS, you get better space management as it relates to varying-length rows and improved performance with mass deletes added to all the existing benefits of partitioned table spaces.

Remember, you can only have one table in a universal table space and you cannot convert an existing segmented or partitioned table space to UTS. You must drop and recreate existing table spaces to use UTS.

To read more about DB2 V9, visit the IBM System Magazine, Mainframe edition Website.