Creation of a table with table-controlled partitioning

Table-controlled partitioning does not require an index for partitioning and is defined by PARTITION clauses on the CREATE TABLE statement.

When you define a partitioning index on a table in a partitioned table space, you specify the partitioning key and the limit key values in the PARTITION clause of the CREATE INDEX statement. This type of partitioning is known as index-controlled partitioning. Because the index is created separately from the associated table, you cannot insert data into the table until the partitioning index is created.

Db2 also supports a method called table-controlled partitioning for defining table partitions. You can use table-controlled partitioning instead of index-controlled partitioning.

With table-controlled partitioning, you identify column values that delimit partition boundaries with the PARTITION BY clause and the PARTITION ENDING AT clause of the CREATE TABLE statement. When you use this type of partitioning, an index is not required for partitioning.

Begin general-use programming interface information.

Assume that you need to create a large transaction table that includes the date of the transaction in a column named POSTED. You want to keep the transactions for each month in a separate partition. To create the table, use the following statement:

CREATE TABLE TRANS
   (ACCTID ...,
    STATE ...,
    POSTED ...,
    ... , ...)
   PARTITION BY (POSTED)
   (PARTITION 1 ENDING AT ('01/31/2003'),
    PARTITION 2 ENDING AT ('02/28/2003'),
    ...
    PARTITION 13 ENDING AT ('01/31/2004'));

End general-use programming interface information.