MAINFRAME > Administrator > Db2

Goodbye Segmented and Partitioned; Hello Universal Table Space

I wrote an article in the July issue of IBM Systems magazine, IBM Z on the use of table-controlled partitioning under DB2 V8 and Universal Table Space (UTS) in DB2 9. A member of the IBM DB2 technical leadership team, Brian Smith, read the article and called me to let me know that I was on the right track. In fact Smith told me the direction is to eliminate segmented and partitioned table spaces altogether and replace them with UTS Partition By Growth (PBG) and Partition By Range (PBR). I asked him if it was going to take 10 years to phase out segmented since it’s taken that long to phase out simple table space. Smith’s response was no.

So what should we expect from IBM in the next version of DB2 (V9 +1)? The current intent is to provide the capability to alter any single-table non-UTS table space to UTS in a future release of DB2. The conversion of simple or segmented will be to UTS PBG, and conversion of a classic partitioned table space will be to UTS PBR via a REORG SHRLEVEL CHANGE.

What this means to customers using packages like PeopleSoft, in which hundreds of tables are stored in a single segmented table space, is that any table space with more than one table will have to be split up into one table space per table. IBM will rely on catalog management tools to help with the split out from multiple tables per table space to one each. Providing this information early gives customers and software vendors enough time to implement a project to split out multi-table segmented table spaces to one table per table space.

When will the use of segmented and classic partitioned table spaces go away? While the future is never crystal clear, IBM has plans to depreciate segmented and classic partition within a few releases just as simple was depreciated in DB2 V9.

Benefits of Change

This conversion to UTS benefits IBM by reducing the cost and overhead of trying to maintain legacy storage structures, which in turn provides more resources to add features that take advantage of the new storage structures. Customers benefit with DB2 V9 through the option to grow the table space when it needs more space or to change the limit keys dynamically or the rotation and reuse of a table space partition, which can greatly reduce the complexity in designing the application to provide the same type of functionality.

The intended direction is to move to a table-level support instead of a table space-level support for space management. There are several reasons for this, including added support for a common set of Data Definition Language (DDL) syntax across the DB2 family and support for real-time statistics at the table level, not just the table space level as it is today. This brings some needed information to improve data usage to in turn improve optimization of data access.

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

comments powered by Disqus



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.


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