Partitioning

Partitioning splits a large table into smaller physical pieces called partitions. Each partition can be managed, reorganized, and backed up separately, which keeps big tables fast and maintainable.

Why partition a table

• Queries that filter on the partitioning key read only the needed partitions instead of the whole table. This is called partition elimination.
• REORG, LOAD, and RECOVERY can run on one partition at a time, so maintenance windows stay short.
• Old data can be dropped by dropping a whole partition instead of deleting millions of rows.
• Each partition can sit on different volumes or storage groups for balanced I/O.

Types of partitioning in DB2

• Range partitioning (table-controlled) :- Rows go to partitions based on key ranges, for example months of a transaction date.
• Partition-by-growth (PBG) :- DB2 adds partitions automatically as the table grows. Simple to manage, good for steadily growing tables.
• Hash partitioning :- Rows spread evenly by a hash of the key. Used in DB2 data sharing for balanced parallelism.
• Index partitioning puts index entries for each table partition into matching index partitions.

Example: range-partitioned table

• Example that partitions an orders table by order year:-
  CREATE TABLESPACE TS_ORDERS IN DBSALES USING STOGROUP SG_SALES PARTITION BY (ORDER_YEAR) (PARTITION 1 ENDING AT (2023), PARTITION 2 ENDING AT (2024), PARTITION 3 ENDING AT (2025)) LOCKSIZE PAGE; CREATE TABLE ORDERS (ORDERNO INTEGER NOT NULL, ORDER_YEAR INTEGER NOT NULL, AMOUNT DECIMAL(11,2)) IN DBSALES.TS_ORDERS;
• DB2 routes each row to the partition whose ENDING AT value covers its key.
• New partitions are added with ALTER TABLESPACE ... ADD PARTITION as data grows.