Why Universal Table Spaces (UTS) ?

by Sowmya Kameswaran and Haakon Roberts

Let’s start with some basics and history. Table space, as the name indicates, is where tables reside. And since tables hold all the great data your company needs and hosts, the way data is stored and organized becomes very important. With the growth in the fields of analytics and explosion in the quantity of data required to run advanced analytics, data stores and decisions around how to organize them are becoming key to the success of companies.

IBM has consistently delivered enhancements in this space to ensure high data availability and that access to data is highly performant and efficient. From a Db2 for z/OS perspective, table spaces have evolved and UTS is now the recommended table organization model. Already a lot of new and useful features are only supported for UTS. Moving forward with Db2 v12 and beyond, conversion of existing table spaces to UTS is going to be key to exploit a lot of exciting new features and functions provided by the engine.

Now that we have a basic idea of what we are going to discuss, lets review the evolution of table spaces.

Non universal table spaces (non UTS):

These have been deprecated for some time but are still supported in Db2. However, simple tablespaces can no longer be created as of V9 and segmented and "classic" partitioned table spaces can no longer be created as of APPLCOMPAT level V12R1M504.

1. Simple table spaces

• They are deprecated as of V9 and can no longer be created, but are still supported by Db2.

• Multiple tables in one tablespace.

• Max of 64 GB can be stored for every table.

• Page based allocation. A page can contain data from multiple tables .

• The primary issue with this data organization model is the fact that rows from different tables can reside in a single page, which means for page level locking that serialization on one table can affect others. Also application performance can suffer, particularly for table space scan operations. Mass delete requires the deletion of each individual row in turn and so on.

2. Segmented

• Multiple tables in one tablespace.

• Segment-based allocation where each segment contains an equal number of pages and each segment contains rows belonging to a single table. Multiple tables in the table space get equal number of segments (SEGSIZE definition) but use only what is needed.

• Max of 64 GB can be stored for every table.

• Pros

  • Since all data for a given table in kept together, table scans become more performant.

  • When tables are dropped entirely, the storage becomes available for immediate reuse without further action.

  • Locking operations only lock segments of a select table without affecting other tables in the tablespace.

  • Mass delete operations are much faster.

• Cons

  • Certain utilities need to operate at tablespace level and hence cause unavailability of multiple tables when executed.

  • If a tablespace becomes unavailable for any reason, it impacts multiple tables.

3. “Classic” Partitioned

• 1 table per tablespace

• Partitions based on boundary values of specific data columns.

• Page based data storage. All pages for a tablespace contain data from only 1 table.

• Recommended to be converted into PBR UTS. Index controlled partitioning must be be converted into table controlled partitioning before conversion to PBR UTS.

• Pros

  • Improved support for larger tables, avoiding 64Gb table space limitation

  • Supports partition independence and partition parallelism to improve application performance as well as utility performance and concurrency. (e.g. Ability to stop and start select partitions without taking down the whole tablespace.)

• Cons

  • More complicated partition and data set managment.

  • Care must be taken to avoid running out of space in partitions.

  • Partitioning key and limit key values must be chosen with care.

Universal table spaces (UTS)

Introduced in Db2 for z/OS V9. Two flavors exist: Partition by Growth (PBG) and Partition by Range (PBR). Each will be discussed in more detail here. UTS was introduced to address the limitations associated with the deprecated table space types reviewed above. Both UTS table space types use a segment-based internal structure and support only a single table within the table space. UTS is the strategic table space type for Db2 base tables. Other table space types (simple, segmented, classic partitioned as mentioned above) are deprecated. It is recommended to convert these to UTS.

1. Partitioned by growth(PBG)

• No defined partitioning scheme

• Allocation starts with partition 1 and new partitions are automatically "grown" as needed up to MAXPARTITIONS.

• Can be created with a pre-defined set of partitions, and new partitions can be allocated manually via DDL if desired.

• All indexes are non-partitioned secondary indexes (NPSIs)

• If not managed well, they can become massive making future maintenance difficult. Size can extend from 64 GB up to 128 TB (much bigger with LOB tablespaces)

• Not ideal for tables containing large amounts of data. Based on extensive learning from IBM experts on the field, it is considered ideal to use PBG TS when data size is <60 GB. Also, single part PBG’s are recommended over multi part PBG’s considering the number of issues and considerations with multi-part.

Ideal for use in the following scenarios:

• For tables that previously resided in simple or segmented table spaces. The only online migration path from simple or segmented is to PBG through pending alter followed by materializing REORG.

• For tables that have no obvious key to partition by.

• When archiving solutions can be exercised for older data which are accessed infrequently. Db2 for z/OS and IBM Db2 Analytics Accelerator provide archiving solutions which still allow the archived data to be accessed easily. Once older partitions are archived , the main tablespaces can be REORG’ed to remove empty partitions and move remaining data into fewer partitions.

• More performant when the table is used primarily for transactional purposes where applications typically access few rows at a time.

2. Partitioned by range

• Partitioning ranges based on a key need to be provided at the time of issuing CREATE or ALTER on the table definition.

• Can use absolute(APN) or relative page numbering (RPN), however, RPN which was introduced in V12 is strongly recommended.

• Changing page numbering (PAGENUM) from relative to absolute or vice versa requires a tablespace level REORG to take effect.

• Indexes can be partitioned.

• Supports online rebalancing of data across partitions and online alter of limit key values to repartition data.

• Supports insertion of a new partition within the partitioning scheme (V12)

• Can reach sizes of up to 128 Terabyte and 1 Peta byte for pages with relative page numbering

• Supports partition independence as well as partition parallelism for utilities to improve concurrency and performance.

Ideal for use in the following scenarios:

• Tables containing large amounts of data.

• Most useful in analytical data access and batch update scenarios where access to large amount of data is required at any given time. Also PBR s allow effective parallel processing of queries and batch jobs across partitions.

• Date-based partitioning provides efficient query execution where the queries typically access more recent data.

3. Partitioned by range RPN

• Supports partition sizes up to 1Tb, giving a total table size of 4Pb.

• Supports online increase of DSSIZE (partition size).

• Supports different DSSIZEs for different partitions

• Can be created as PBR RPN or existing classic partitioned or PBR APN table spaces can be converted to PBR RPN via ALTER followed by a materializing REORG.

Why UTS?

With the steady growth of the amount and criticality of enterprise data, availability and performance are becoming increasingly important.

The simple answer to this question is that non-UTS table space types (with the exception of LOB) are deprecated. New table spaces should be UTS. In fact, as of APPLCOMPAT V12R1M04, simple, segmented and classic partitioned table spaces can no longer be created.

Also, as mentioned earlier, many new features are only made available for UTS table spaces and IBM's development and test focus is naturally upon non-deprecated table space types.

A better question is perhaps why UTS was introduced and why the other types were deprecated in the first place? This article has laid out the key attributes of each. UTS was designed to address the limitations and drawbacks of the earlier table space types and will continue to be enhanced further.

A lot of recent focus has been placed upon the ability to seamlessly convert existing table spaces to UTS, culminating in the ability to move tables from multi-table simple and segmented table spaces to PBG via online REORG delivered in V12R1M508.

Conversion of non UTS to UTS

This can be effected by:

• Executing an ALTER on a single table segmented or simple TS (adding MAXPARTITIONS) followed by an online REORG which converts it into a PBG UTS.

• Executing an ALTER on a partitioned table-controlled table space (Adding SEGSIZE) followed by an online REORG which converts it into a PBR UTS.

• Db2 for z/OS FL 508 supports migration of multi table tablespaces to PBG UTS with MOVE TABLE option of ALTER TABLESPACE. The ALTER needs to be followed by a REORG to materialize the changes.

• Prior to FL 508, Unload/Drop/Create/Re-load for multi table segmented tablespace where a partition by growth tablespace is created for each table.

PBG -> PBR conversion (and vice versa)

As of today, conversion between PBG and PBR and vice versa can only be achieved by the UNLOAD/DROP/CREATE/LOAD route.

In Apollo (Db2 for z/OS vNext) this is being enhanced to support online conversion from PBG to PBR. As mentioned above, PBG lends itself particularly to relatively small tables and PBR to larger tables. For situations where a table has grown to such an extent that PBR would be more desirable, no online conversion method exists today to convert from PBG to PBR. This enhancement will address that issue. In future, one may expect that Db2 would also provide the ability to convert from PBR to PBG, however it is clear that the primary concern is tables that currently reside in PBGs.

Such conversion without the means of online REORG would require an application outage, or other methods to resynchronize data to avoid lost updates that may have occurred during the conversion process.

Bottom line (TLDR)

If not already started or done, start moving from non UTS to UTS. With the explosion in the sheer volume of data and also the need to support very data intensive applications, the benefits are just endless. Also the conversion will allow you to exploit all feature and functional enhancements provided by the engine.

For existing tables, conversion to PBG or PBR is effectively chosen for you since online conversion from segmented or simple is only to PBG whereas online conversion from classic partitioned is only to PBR.

If converting to PBR, choose PBR RPN.

If creating a new table space, choose carefully between PBG and PBR. For small tables, PBG makes sense. For tables that are anticipated to grow large, PBR is the best choice.

References:

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/admin/src/tpc/db2z_typesofdb2tablespaces.html

http://robertsdb2blog.blogspot.com/2014/06/db2-for-zos-getting-to-universal-table.html

http://www.mdug.org/data/documents/Db2-Tablespace-Types-Overview.pdf