I believe that the future for all DB2 data storage on z/OS is in universal table space (UTS). This may be a strong statement, but as I look at IBM's major DB2 enhancements over the past few years, they revolve around UTS.
A UTS is a table space that is both segmented and partitioned. It can be defined as either partitioned-by-growth or range-partitioned. In a short post back in July 2007, I mentioned that the one drawback with UTSs is that they must contain only one table per table space.
While we DB2 for z/OS DBAs have been making the recommendation of one table per table space for years, some vendors have ignored this rule of thumb, delivering table spaces with thousands of tables in them. Having one table per table space is desirable because, among other reasons, it simplifies maintenance (backup, restore, reorg). In addition, with partitioning you not only gain the benefit of much larger table size but improved availability and access to performance-running utilities such as image copy and reorg.
At a recent Midwest Database User Group meeting in Chicago, IBM's Roger Miller presented on the topic of migrating from simple, segmented and partitioned table space to either UTS range-partitioned or UTS partition by growth table space. This will be enabled by using the ALTER table space statement, which is expected to be part of the next DB2 release (DB2 X for z/OS).
DBAs generally conduct benchmarks before and after making any structural changes, and you should, if possible, run tests in parallel on both the classic and UTS table sets in your development environment before altering table spaces in production. It's important to note that once you use either ALTER table space option (range-partitioned or partition by growth), you won't be able to convert back to your previous table space type (simple, segmented or partitioned). If you use ALTER table space, have problems and decide you want to fall back, your only option will be to unload, drop, create and reload the data.
Making ALTER table space a one-way conversion seems an odd choice, but I imagine it's because IBM wants to move forward and standardize table file structure to simplify its development processes and reduce costs. I'm sure it takes a lot of code to support simple, segmented and partitioned table spaces.
When you consider that new DB2 9 for z/OS features like clone and XML data type require UTS, it makes sense that the need to convert to UTS will grow with future DB2 releases. With ALTER table space, IBM is providing its customers with the migration path to UTS.
A UTS is a table space that is both segmented and partitioned. It can be defined as either partitioned-by-growth or range-partitioned. In a short post back in July 2007, I mentioned that the one drawback with UTSs is that they must contain only one table per table space.
While we DB2 for z/OS DBAs have been making the recommendation of one table per table space for years, some vendors have ignored this rule of thumb, delivering table spaces with thousands of tables in them. Having one table per table space is desirable because, among other reasons, it simplifies maintenance (backup, restore, reorg). In addition, with partitioning you not only gain the benefit of much larger table size but improved availability and access to performance-running utilities such as image copy and reorg.
At a recent Midwest Database User Group meeting in Chicago, IBM's Roger Miller presented on the topic of migrating from simple, segmented and partitioned table space to either UTS range-partitioned or UTS partition by growth table space. This will be enabled by using the ALTER table space statement, which is expected to be part of the next DB2 release (DB2 X for z/OS).
DBAs generally conduct benchmarks before and after making any structural changes, and you should, if possible, run tests in parallel on both the classic and UTS table sets in your development environment before altering table spaces in production. It's important to note that once you use either ALTER table space option (range-partitioned or partition by growth), you won't be able to convert back to your previous table space type (simple, segmented or partitioned). If you use ALTER table space, have problems and decide you want to fall back, your only option will be to unload, drop, create and reload the data.
Making ALTER table space a one-way conversion seems an odd choice, but I imagine it's because IBM wants to move forward and standardize table file structure to simplify its development processes and reduce costs. I'm sure it takes a lot of code to support simple, segmented and partitioned table spaces.
When you consider that new DB2 9 for z/OS features like clone and XML data type require UTS, it makes sense that the need to convert to UTS will grow with future DB2 releases. With ALTER table space, IBM is providing its customers with the migration path to UTS.
Great news, though it'd be nice if that Alter came in a ptf instead of having to wait for the next big release.
Posted by: Paul | October 27, 2009 at 02:40 PM