Creating partition-by-growth table spaces

You can create a partition-by-growth table space so that Db2 manages partitions based on data growth and uses segmented space management capabilities within each partition.

About this task

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: To 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.

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: PBG table spaces are the suggested alternative for single-table Db2-managed segmented (non-UTS) table spaces, which are deprecated.

Procedure

To create a partition-by-growth table space, use one of the following approaches:

Issue a CREATE TABLE statement, and specify the PARTITION BY SIZE clause.
Db2 implicitly creates a partition-by-growth table space for the new table.
The following example creates a table with partitions based on data growth, which resides in an implicitly created partition-by-growth table space:
```
  CREATE TABLE TS02TB
       (C1 SMALLINT,
        C2 DECIMAL(9,2),
        C3 CHAR(4))
     PARTITION BY SIZE EVERY 4G
     IN DATABASE DSNDB04;
```
Issue a CREATE TABLESPACE statement and specify any of the following combinations of the MAXPARITIONS and NUMPARTS clauses:
- Specify MAXPARTITIONS without NUMPARTS, for example:
```
CREATE TABLESPACE TEST01TS IN TEST01DB USING STOGROUP SG1 
DSSIZE 2G 
MAXPARTITIONS 24 
LOCKSIZE ANY 
SEGSIZE 4; 
COMMIT;
```
- Specify both MAXPARTITIONS and NUMPARTS.
- FL 504 Omit both MAXPARTITIONS and NUMPARTS.