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:
  • 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
Tip: PBG table spaces are best used for small to medium-sized tables. If you expect a table to grow much larger than the 64 GB, consider using a partition-by-range (PBR) table space instead.

Non-UTS table space types

Deprecated function: Start of changeFL 504 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).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 the UTS 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 Start of changeCan use absolute or relative page numbering.End of change
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.Start of change1End of change
Segmented (non-UTS) table space Yes No This type is deprecated.Start of change1End of change
Simple table space No No This type is deprecated.2
Notes:
  1. Start of changeFL 504 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).End of change
  2. 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.

G
Partition-by-growth table space (PBG UTS)
R
Partition-by-range table space (PBR UTS)
O
LOB table space
P
XML table space
L
Partitioned (non-UTS) table space created with the LARGE option (deprecated)
blank
One of the following deprecated types:
  • Partitioned (non-UTS) table space
  • Segmented (non-UTS) table space
  • Simple table space
Start of change

Non-large table spaces (deprecated)

A non-large table space has 4-byte row identifiers (RIDs) and is limited to a maximum size of 64 GB. The following types of table spaces can be non-large table spaces:

  • A partitioned (non-UTS) table space, if neither DSSIZE nor LARGE were specified when it was created
  • All segmented (non-UTS) table spaces
  • All simple table spaces

All PBG and PBR UTS, and any non-UTS table spaces that were created with either DSSIZE or LARGE specified, are considered large table spaces because they use 5-byte or 7-byte RIDs and can contain more than 64 GB of data.

The Db2 product documentation rarely mentions the large table space attribute or because it always applies to all table space types that are not deprecated. It was originally introduced in DB2 version 5, but it was soon replaced by the DSSIZE clause . A large table space was originally created by specifying CREATE LARGE TABLESPACE. The LARGE keyword is tolerated for compatibility with DB2 version 5, but only if the DSSIZE clause is not specified. The DSSIZE clause is recommended for specifying a 4 GB or larger maximum partition size.

End of change