Table space types and characteristics in Db2 for z/OS

Db2 supports several different types of table spaces. The partitioning method and segmented organization are among the main characteristics that define the table space type.

Advantages of universal table spaces

Universal table spaces (UTS) combine the benefits of data partitions and segmented organization. Each UTS table space always contains only a single table.

Universal table spaces offer the following advantages, when compared to the deprecated non-UTS table space types:

A choice of partitioning methods:
- Partitions based on ranges of data values, with Partition-by-range table spaces
- Partitions based on data growth and automatically managed by Db2, with Partition-by-growth table spaces
Improved space management for varying-length rows because a segmented space-map page has more information about free space than a partitioned space-map page
Improved mass delete performance because mass delete in a segmented table space organization tends to be faster than in non-segmented table space organizations
Table scans that are localized to segments
Immediate reuse of all or most of the segments of a table after the table is dropped or mass deleted

Non-UTS table space types

Deprecated function: Start of change

FL 504 Non-UTS table spaces for base tables are deprecated. In general, for packages bound with APPLCOMPAT(V12R1M504) or higher, the result of a CREATE TABLESPACE statement is always a partition-by-growth or partition-by-range UTS table space, and a CREATE TABLE statement that specifies a non-UTS table space, including existing multi-table segmented table spaces, returns an error. The only exception is a dynamic CREATE TABLESPACE statement that is executed after the CURRENT APPLICATION COMPATIBILITY special register is set to a value lower than V12R1M504. In this case, CREATE TABLESPACE creates a non-UTS table space, and a CREATE TABLE statement can specify a non-UTS table space. Existing tables in non-UTS table spaces remain supported. However, support is likely to be removed in the future. End of change

Partitioned (non-UTS) table spaces use partitions based on ranges of data values, like partition-by-range table spaces, but they do not use segmented organization. Segmented (non-UTS) table spaces store the data from separate tables in different segments, but they cannot be partitioned. Simple table spaces are not partitioned or segmented.

Tip: For best results, convert all simple and other non-UTS table spaces to PBG or PBR as soon as possible. For more information, see Converting deprecated table spaces to fully supported types.

Comparison of table space types

The following table compares the characteristics of the various table space types that Db2 for z/OS supports.

Table 1. Comparison of table space types
Type	Segmented?	Partitioned?	Remarks
Partition-by-range table space	Yes	Yes, based on data value ranges	Can use absolute or relative page numbering.
Partition-by-growth table space	Yes	Yes, based on data growth
LOB table space	No	No	For auxiliary tables that contain the data for LOB columns.
XML table space	Yes	Yes, based on the partitioning method of the base table.	UTS table spaces that are created implicitly for XML data.
Partitioned (non-UTS) table space	No	Yes, based on data value ranges	This type is deprecated.¹
Segmented (non-UTS) table space	Yes	No	This type is deprecated.¹
Simple table space	No	No	This type is deprecated.²

Notes:

FL 504 Non-UTS table spaces for base tables are deprecated. In general, for packages bound with APPLCOMPAT(V12R1M504) or higher, the result of a CREATE TABLESPACE statement is always a partition-by-growth or partition-by-range UTS table space, and a CREATE TABLE statement that specifies a non-UTS table space, including existing multi-table segmented table spaces, returns an error. The only exception is a dynamic CREATE TABLESPACE statement that is executed after the CURRENT APPLICATION COMPATIBILITY special register is set to a value lower than V12R1M504. In this case, CREATE TABLESPACE creates a non-UTS table space, and a CREATE TABLE statement can specify a non-UTS table space. Existing tables in non-UTS table spaces remain supported. However, support is likely to be removed in the future.
Db2 12 does not support creating simple table spaces. Existing simple table spaces remain supported, but they are likely to be unsupported in the future.

Determining the table space type

The TYPE column of the SYSIBM.SYSTABLESPACE catalog table indicates the type of each table space.

blank: The table space was created without the LOB or MEMBER CLUSTER options. If the DSSIZE column is zero, the table space is not greater than 64 gigabytes.
G: Partition-by-growth table space.
L: The table space can be greater than 64 gigabytes.
O: The table space was defined with the LOB option (the table space is a LOB table space).
P: Implicit table space created for XML columns.
R: Partition-by-range table space.