Partition-by-growth table spaces

A partition-by-growth table space is a universal table space (UTS) that has partitions that Db2 manages automatically based on data growth. It holds data pages for only a single table, and has segmented space management capabilities within each partition.

Db2 manages partition-by-growth table spaces automatically as data grows, by automatically adding a new partition when more space is needed to satisfy an insert operation.

Partition-by-growth table spaces are best used when a table is expected to exceed 64 GB, or when a table does not have a suitable partitioning key. Partition-by-growth table spaces can grow up to 128 TB, depending on the buffer pool page size used, and the MAXPARTITIONS and DSSIZE values specified when the table space is created.

The partitioned structure supports partition-level utility operations and parallelism capabilities. A partition-by-growth table space also has segmented organization and segmented space management capabilities within each partition. The segmented structure provides better space management and mass delete capabilities.

Tip: Start of changePartition-by-growth table spaces are the suggested alternative for single-table Db2-managed segmented (non-UTS) table spaces, which are deprecated.End of change

You can explicitly create partition-by-growth table spaces by issuing CREATE TABLESPACE statements, or Db2 can create them for you when you issue CREATE TABLE statements. For instructions, see Creating partition-by-growth table spaces

Restrictions for partition-by-growth table spaces:

The following restrictions apply to partition-by-growth table spaces:

  • Db2 must manage space for the partitions (it cannot be user-managed) so that Db2 can create new data sets as partitions become full.
  • Partitions cannot be explicitly rotated, or altered. That is, ALTER TABLE statements that specify ALTER PARTITION or ROTATE PARTITION cannot be used for partition-by-growth table spaces.
  • The PART option of the LOAD utility is not supported.
  • The REBALANCE option of the REORG utility is not supported.
  • A non-partitioning index (NPI) always uses a 5 byte record identifier (RID).
  • Partitioned indexes are not supported.