DB2 partitioning: PBR, PBG, and partition independence

Why partitioned tables exist

One VSAM data set has a size ceiling. Utilities on a 10 TB heap would run forever and drain the whole table. Data partitioning makes each partition a unit of space, I/O, and often of administration. SQL still sees one table. The optimizer can skip parts (partition elimination / partition pruning) when predicates match the partitioning key.

PBR: range partitioning

A PBR universal table space is segmented inside each range partition. You supply partition boundaries with PARTITION BY RANGE and ENDING AT values. Last part often uses MAXVALUE.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLESPACE TSACCT
  IN HRDB
  USING STOGROUP HRSTO
  NUMPARTS 4
  SEGSIZE 32
  DSSIZE 64 G;
 
CREATE TABLE HR.ACCOUNT
  (ACCT_NUM INT NOT NULL,
   NAME     VARCHAR(40),
   BALANCE  DECIMAL(11,2))
  IN HRDB.TSACCT
  PARTITION BY RANGE (ACCT_NUM)
   (PARTITION 1 ENDING AT (199),
    PARTITION 2 ENDING AT (299),
    PARTITION 3 ENDING AT (399),
    PARTITION 4 ENDING AT (MAXVALUE));

NUMPARTS must match the number of PARTITION clauses. Choose a key that queries actually filter (account, date, region). A random clustering key gives you extra data sets but no pruning.

PBR can use relative page numbering (RPN) or absolute. RPN supports larger partitions and immediate DSSIZE increases on a single part without a REORG (when you are raising the limit). Prefer RPN for new large PBR spaces.

PBG: growth partitioning

A PBG space has no range key. Db2 adds a partition when an insert needs space, up to MAXPARTITIONS. Each part is limited by DSSIZE. Good for medium tables without a natural range. IBM’s tip: if you expect far more than tens of GB, consider PBR instead—PBG partition-level operations are weaker because rows are not grouped by a business key.

1
2
3
4
5
6
CREATE TABLESPACE TSPBG
  IN HRDB
  USING STOGROUP HRSTO
  MAXPARTITIONS 16
  DSSIZE 4 G
  SEGSIZE 32;

PBG restrictions: you cannot ROTATE or ALTER PARTITION ranges. Space must be Db2-managed (STOGROUP) so Db2 can allocate the next data set. LOAD REPLACE of a PBG has extra rules versus PBR.

DSSIZE, NUMPARTS, MAXPARTITIONS

DSSIZE is per-partition maximum, not the whole table. Page size and numbering scheme affect the legal DSSIZE. Raising DSSIZE on RPN PBR can be immediate; lowering it usually needs REORG. MAXPARTITIONS is a ceiling, not a promise that all parts exist today.

Partition maintenance, utilities, recovery, locking

Partition-level utilities use PART or DSNUM so COPY/REORG/RUNSTATS hit one data set. That is partition independence: part 3 COPY-pending does not have to block part 7. Partition-level recovery restores one DSNUM then applies log; indexes must match—DPSI parts recover with the data part more cleanly than a single NPSI.

Partition rotation (PBR): ALTER TABLE … ROTATE PARTITION FIRST TO LAST empties the oldest range and redefines it as the new high boundary—classic for monthly history. Not valid for PBG.

Partition-level locking: with partitioned spaces, a drain or lock can target a partition so SQL on other ranges continues. LOCKSIZE PAGE/ROW still applies inside the part. A tablespace-level lock is the blunt instrument you try to avoid.

Partition-level statistics tell the optimizer that part 1 has 90% of the rows. Without them, pruning still skips parts, but join cardinality can be wrong.

1
2
3
4
5
COPY TABLESPACE HRDB.TSACCT DSNUM 2
     COPYDDN(SYSCOPY) FULL YES SHRLEVEL REFERENCE
 
REORG TABLESPACE HRDB.TSACCT PART 2
     SHRLEVEL CHANGE

Partition pruning and indexes

Partition pruning / elimination needs predicates on the partitioning columns that the optimizer can use (literals, host variables, sometimes between). A predicate only on NAME will scan all PBR parts. EXPLAIN shows which parts are accessed.

Index partitioning: a partitioning index used to be required for index-controlled partitioning (legacy). Table-controlled PBR does not need a partitioning index, but you almost always want indexes for access paths.

• Partitioned indexes (DPSI) — index data sets align with table parts; utilities stay partition-independent
• Nonpartitioned indexes (NPSI) — one index for the whole table; uniqueness across parts; REORG of one data part must still maintain the whole NPSI

Design rule: partition on the column you filter in utilities and SQL; put uniqueness that is not the partition key on an NPSI only when you must; accept the utility coupling that creates.

Explain It Like I'm Five

A partitioned table is a row of lockers instead of one giant toy chest. PBR labels lockers “accounts 1–199,” “200–299,” and so on, so you only open the locker you need (pruning). PBG is a row of extra empty lockers the janitor unlocks when the current one is stuffed—no labels, just more space. COPY PART 2 photographs one locker. ROTATE takes the oldest locker, dumps it, and relabels it as next month. A partitioned index is a mini-list taped inside each locker. A nonpartitioned index is one giant list on the wall for the whole hallway—great for “is this name unique?” and annoying when you only wanted to clean locker 2.

Exercises

1. Write PBR DDL with four ranges on a DATE column and NUMPARTS 4.
2. Write PBG DDL with MAXPARTITIONS 8 and DSSIZE 8 G. State two PBG restrictions.
3. Explain why RECOVER DSNUM 3 plus an NPSI is harder than with a DPSI.
4. Sketch a ROTATE PARTITION strategy for 12 monthly parts.
5. For a query WHERE ACCT_NUM = 250, which PBR parts should be pruned, using the sample boundaries above?