Partitioned Tablespaces

A partitioned tablespace splits one very large table into partitions. Each partition can be managed on its own.

What is a partitioned tablespace

• A partitioned tablespace is primarily used for very large tables.
• The tablespace is divided into partitions, from 1 to 64 in classic partitioned spaces.
• The entire tablespace can have only one table.
• Each partition contains rows with boundary values of specific columns, called the partitioning key.
• The NUMPARTS parameter of the tablespace definition decides the number of partitions.

Defining partitions with PARTITION BY

• When the table is created, the PARTITION BY clause defines the partitioning key and the limit key values for each partition:-
• CREATE TABLESPACE PARTS
     IN MM01DB
     USING STOGROUP SYSDEFLT
     NUMPARTS 4
     PRIQTY 100
     SECQTY 100;
  
  CREATE TABLE MM01.SALES
  (SALE_DATE   DATE           NOT NULL,
   SALE_ID     INTEGER        NOT NULL,
   AMOUNT      DECIMAL(11,2)  ,
   PRIMARY KEY (SALE_DATE, SALE_ID))
  IN MM01DB.PARTS
  PARTITION BY (SALE_DATE)
  (PARTITION 1 ENDING AT ('2025-12-31'),
   PARTITION 2 ENDING AT ('2026-06-30'),
   PARTITION 3 ENDING AT ('2026-12-31'),
   PARTITION 4 ENDING AT (MAXVALUE));
• DB2 routes each row to the partition whose limit key range contains the partitioning key value.
• Queries that filter on the partitioning key can skip partitions, which keeps large-table queries fast.
• The partitioning key should be a column used in most queries, such as a date or a region code.

Benefits of partitioning

• Utilities can be run on one partition at a time, so a REORG or COPY of a huge table finishes faster.
• Individual partitions can be independently recovered and reorganized.
• Old partitions can be archived or dropped without touching the rest of the table.
• Reorganizing the tablespaces restores every table to its clustered order, and with partitions this can be done partition by partition.