Partition-by-growth table spaces

A partition-by-growth (PBG) 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 PBG table spaces automatically as data grows, by automatically adding a new partition when more space is needed to satisfy an insert operation.

PBG table spaces are best used for small or medium sized tables, especially 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.

Any index created on a table in a PBG table space must be a non-partitioned index. That is, partitioned indexes, including partitioning indexes and data-partitioned secondary indexes (DPSIs) are not supported on table in PBG table spaces. For more information, see Indexes on partitioned tables.

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.
When a table in a PBG table space grows too large, several drawbacks can begin to arise, including the following issues:
  • Insert and query performance degradation, which is perhaps the most important factor suggesting that conversion is required. Such performance degradation can have many causes, but for large tables in PBG tables spaces, the size of the table space is often one of the major causes.
  • Difficulty regaining clustering of the data (which requires a REORG of the entire table space).
  • Problems associated with very large non-partitioned indexes, because partitioned (partitioning and DPSI) indexes are not supported for tables in PBG table spaces. For more information, see
  • Lack of partition parallelism support for utilities.
  • Limited support for partition-level utility operations.

If you encounter these issues, consider using partition-by-range (PBR) table spaces instead.

Tip: Start of changeTo use a PBR table space for a table without a naturally suitable partitioning scheme, consider creating the table with an implicitly hidden ROWID column in the partitioning key. Any ROWID column in the partitioning key guarantees a very even distribution of data across the partitions, and an implicitly-hidden ROWID column can also be transparent to applications.End of change

The partitioned structure supports partition-level utility operations and parallelism capabilities. A PBG 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 changePBG 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 PBG 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 PBG 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 PBG 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.