Table Partitioning

Table partition also known as Range Partition is a data organization scheme in which table data is divided across multiple storage objects, called data partitions, according to values in one or more table columns. These storage objects can be in different table spaces, in the same table space, or a combination of both.

Create partitioned table
The table can be partitioned using the PARTITION BY RANGE clause in the CREATE TABLE statement.

Example CREATE TABLE ORDERS(order_id INT NOT NULL,                    order_shipment DATE NOT NULL,                     order_amount INT NOT NULL) PARTITION BY RANGE (order_shipment) (PARTITION Quarter1_2010 STARTING ('01/01/2010') ENDING ('03/31/2010'),          PARTITION Quarter2_2010 STARTING ('04/01/2010') ENDING ('06/30/2010'),           PARTITION Quarter3_2010 STARTING ('07/01/2010') ENDING ('09/30/2010'),           PARTITION Quarter4_2010 STARTING ('10/01/2010') ENDING ('12/31/2010'));

Partition Information
The details of the partitions will be stored in SYSCAT.DATAPARTITIONS catalog view.

Example SELECT * FROM SYSCAT.DATAPARTITIONS WHERE TABNAME='ORDERS';

Indexes on partitioned table
Partitioned tables can have indexes that are nonpartitioned, partitioned, or both.

Partitioned Index
A partitioned index is made up of a set of index partitions, each of which contains the index entries for a single data partition. Each index partition contains references only to data in its corresponding data partition. Both system- and user-generated indexes can be partitioned.

Example CREATE INDEX Order_shipment ON ORDERS(order_shipment) PARTITIONED;

Nonpartitioned Index
A nonpartitioned index is a single index  object that refers to all rows in a partitioned table.

Example CREATE INDEX Order_shipment ON ORDERS(order_shipment) NOT PARTITIONED;

Operation on partitions
A partitioned table will experience various operations, such as attaching a data partition, detaching a partition, and reorganizing a partition.

Attach partition(Roll-In)
Table partitioning allows the efficient roll-in of data partition. The ATTACH PARTITION clause in the ALTER TABLE statement makes data roll-in very easy. It takes the existing table (source table) and attaches it to the target table as a new data partition.

Example ALTER TABLE ORDERS ATTACH PARTITION Quarter1_2011 STARTING FROM ('01/01/2011') ENDING AT ('03/31/2011') FROM order_details_quarter1_2011; SET INTEGRITY FOR ORDERS IMMEDIATE CHECKED;

Detach partition(Roll-Out)
Table partitioning also allows the efficient roll-out of data partition. The DETACH PARTITION clause in the ALTER TABLE statement makes data roll-out very easy.

Example ALTER TABLE ORDERS DETACH PARTITION Quarter1_2010 INTO order_details_quarter1_2010;

Drop partitioned table
Partition table can be dropped by DROP TABLE statement. Example DROP TABLE ORDERS;

developerWorks Article

 * Distributed DBA: Creating and Using Partitioned Tables
 * Unleash the power of table partitioning in your DB2 warehouse
 * DB2 9 table partitioning
 * Table partitioning in DB2 9

Also See

 * Catalog Views
 * Code Snippets
 * DB2 Product Demonstration
 * DB2 FAQs