Module 7: DB2 Tables and Tablespaces

Universal Tablespaces

A universal tablespace is the modern tablespace type. It grows easily and still supports partition-level utilities.

A universal tablespace is available from DB2 9 for z/OS and is the recommended type for new tables.
It behaves like a segmented tablespace for growth and like a partitioned tablespace for utilities.
There are two flavors: partition-by-growth (PBG) and partition-by-range (PBR).
Unlike classic partitioned spaces, a universal tablespace can hold more than one table when it is partition-by-growth.

In a partition-by-growth tablespace, DB2 adds new partitions automatically as the data grows.
You define MAXPARTITIONS instead of a fixed partition count, and DB2 creates partitions on demand.
PBG is ideal when you do not know how large the table will become:-
CREATE TABLESPACE PBGS
   IN MM01DB
   USING STOGROUP SYSDEFLT
   SEGSIZE 32
   MAXPARTITIONS 100
   PRIQTY 100
   SECQTY 100;
The SEGSIZE clause organizes the space into segments, one table per segment, like a segmented tablespace.

In a partition-by-range tablespace, you define the partitions and their limit keys up front, like a classic partitioned space.
PBR supports up to 4096 partitions, far more than the 64 of a classic partitioned tablespace.
PBR is ideal for very large tables with a known partitioning key, such as a date column:-
CREATE TABLESPACE PBRS
   IN MM01DB
   USING STOGROUP SYSDEFLT
   SEGSIZE 64
   NUMPARTS 12
   PRIQTY 100
   SECQTY 100;

CREATE TABLE MM01.ORDERS
(ORDER_DATE  DATE          NOT NULL,
ORDER_ID    INTEGER       NOT NULL,
AMOUNT      DECIMAL(11,2) ,
PRIMARY KEY (ORDER_DATE, ORDER_ID))
IN MM01DB.PBRS
PARTITION BY RANGE (ORDER_DATE)
(PARTITION 1 ENDING AT ('2026-01-31'),
PARTITION 2 ENDING AT ('2026-02-28'),
PARTITION 3 ENDING AT ('2026-03-31'),
PARTITION 4 ENDING AT (MAXVALUE));
Utilities such as REORG, COPY and RECOVER can run on individual partitions, just like classic partitioned spaces.