Partitioned (non-UTS) table spaces (deprecated)

A partitioned (non-UTS) table space stores data pages for a single table. Db2 divides the table space into partitions. Non-UTS table spaces for base tables are deprecated and likely to be unsupported in the future.

Deprecated function: Non-UTS table spaces for base tables are deprecated. CREATE TABLESPACE statements that run at application compatibility level V12R1M504 or higher always create a partition-by-growth or partition-by-range table space, and CREATE TABLE statements that specify a non-UTS table space (including existing multi-table segmented table spaces) return an error. However, you can use a lower application compatibility level to create table spaces of the deprecated types if needed, such as for recovery situations. For instructions, see Creating non-UTS table spaces (deprecated).
Tip: Convert existing partitioned (non-UTS) spaces to partition-by-range table spaces as soon as possible, as described in Converting partitioned (non-UTS) table spaces to partition-by-range universal table spaces.

The partitions are based on the boundary values that are defined for specific data columns. Utilities and SQL statements can run concurrently on each partition.

In the following figure, each partition contains one part of a table.

Figure 1. Pages in a partitioned table space
Begin figure description. This figure shows a partitioned table space, where each partition contains one part of a table. End figure description.

Characteristics of partitioned (non-UTS) table spaces

Partitioned (non-UTS) table spaces have the following characteristics:

  • You can plan for growth. When you define a partitioned table space, Db2 usually distributes the data evenly across the partitions. Over time, the distribution of the data might become uneven as inserts and deletes occur.

    You can rebalance data among the partitions by redefining partition boundaries with no impact to availability. You can also add a partition to the table and to each partitioned index on the table; the new partition becomes available immediately.

  • You can spread a large table over several Db2 storage groups or data sets. The partitions of the table do not all need to use the same storage group.
  • Partitioned table spaces let a utility job work on part of the data while allowing other applications to concurrently access data on other partitions. In that way, several concurrent utility jobs can, for example, load all partitions of a table space concurrently. Because you can work on part of your data, some of your operations on the data might require less time.
  • You can use separate jobs for mass update, delete, or insert operations instead of using one large job; each smaller job can work on a different partition. Separating the large job into several smaller jobs that run concurrently can reduce the elapsed time for the whole task.

    If your table space uses nonpartitioned indexes, you might need to modify the size of data sets in the indexes to avoid I/O contention among concurrently running jobs. Use the PIECESIZE parameter of the CREATE INDEX or the ALTER INDEX statement to modify the sizes of the index data sets.

  • You can put frequently accessed data on faster devices. Evaluate whether table partitioning or index partitioning can separate more frequently accessed data from the remainder of the table. You can put the frequently accessed data in a partition of its own. You can also use a different device type.
  • You can take advantage of parallelism for certain read-only queries. When Db2 determines that processing is likely to be extensive, it can begin parallel processing of more than one partition at a time. Parallel processing (for read-only queries) is most efficient when you spread the partitions over different disk volumes and allow each I/O stream to operate on a separate channel.

    Use the Parallel Sysplex® data sharing technology to process a single read-only query across many Db2 subsystems in a data sharing group. You can optimize Parallel Sysplex query processing by placing each Db2 subsystem on a separate central processor complex.

  • Partitioned table space scans are sometimes less efficient than table space scans of segmented table spaces.
  • Db2 opens more data sets when you access data in a partitioned table space than when you access data in other types of table spaces.
  • Nonpartitioned indexes and data-partitioned secondary indexes are sometimes a disadvantage for partitioned tables spaces.