Bookmark and Share
RSS

Recent Posts


Why You Should Move to Universal Table Space

July 26, 2016

Almost six years ago, I wrote that the future for all DB2 data storage on z/OS will be Universal Table Space (UTS). The purpose of this post isn't to pat myself on the back, but to note that the future is here.

Of course we still have legacy table spaces (simple, segmented and classic partition table spaces). But all DB2 enhancements over the past few years have been focused on UTS.

Here are just some of the features from the past three releases that require UTS:
  • Partition-by-growth table spaces (DB2 9)
    • Eliminates the 64 GB size limit for table spaces that are not range-partitioned.
  • Clone tables (DB2 9)
    • Super-quick, super-easy “switch out” of old data for new in a table.
  • Hash-organized tables (DB2 10)
    • Super-efficient row access via an “equals” predicate and a unique key.
  • “Currently committed” locking behavior (DB2 10)
    • Retrieve committed data from a table without being blocked by inserting and deleting processes.
  • Pending DDL (DB2 10)
    • Online alteration of table space characteristics such as DSSIZE, SEGSIZE and page size – via ALTER and online REORG.
  • LOB in-lining (DB2 10)
    • Store all or part of smaller LOB values physically in base table vs. LOB table space.
  • XML multi-versioning (DB2 10)
    • Better concurrency for XML data access, and supports XMLMODIFY function.
  • ALTER TABLE with DROP COLUMN (DB2 11)
    • An online change, thanks to this being pending DDL. (This and other changes that use online REORG to materialize the changes are part of what's called online-schema evolution.)
That's by no means a complete list, but if your organization hasn't already migrated to UTS, you should get the point: It's time to take action. You should start planning on how you'll move forward from your existing legacy table spaces.
 
While it'd be an exaggeration to label any migration process as simple, moving to UTS is much easier with the capabilities brought to DB2 10. Here are the conversion options:

Single-table segmented or simple to universal partition-by-growth (PBG).
  • ALTER TABLESPACE with MAXPARTITIONS specification. DB2 will automatically convert the table space to UTS. Make sure you set the MAXPARTITION to 1 or a minimum number. Have a larger number such as max value of 4096 will cost you memory and CPU.
Table-controlled partitioned table space to universal partition-by-range (PBR).
  • ALTER TABLESPACE with SEGSIZE specification. When you specify a SEGSIZE on a partitioned table space DB2 will convert it to a PBR.  In most cases a segsize of 64 would be recommended. The only time you would go smaller would be if you have less than 128 pages. When was the last time you've seen a table that small.
Once you convert from legacy to UTS, the current active packages are marked invalid. It's best to REBIND these packages manually. To determine which packages are impacted, query dependencies in the SYSIBM.SYSPACKDEP table.

If you're using a vendor application containing multiple tables in a single table space, consider splitting out those tables that hold active data. I did this several years ago when I was a DBA supporting PeopleSoft applications. We saved only the tables we needed. You too can control the physical structures for all your applications, including those from third party vendors.

It's past time to get on board with UTS. If you're at least on DB2 10, the migration process isn't that complex, and you simply can't afford to miss out on all the critical DB2 function that requires UTS So start planning your migration now.

Posted July 26, 2016| Permalink

comments powered by Disqus