Table-Controlled Partitioning

Table-controlled partitioning (TCP) lets you control partition boundaries for a table using values defined in the CREATE TABLE statement. This contrasts with index-controlled partitioning (ICP), where partition boundaries are controlled by CREATE INDEX statement values.
carcudb2
Table-controlled partitioning (TCP) lets you control partition boundaries for a table using values defined in the CREATE TABLE statement. This contrasts with index-controlled partitioning (ICP), where partition boundaries are controlled by CREATE INDEX statement values.
TCP is more flexible than ICP. With TCP, you can create more than one partitioned index in a tablespace, and a partitioning and clustering index are not required. For a complete discussion of TCP, including the differences between TCP and ICP, see the IBM 
DB2 Universal Database for z/OS Administration Guide
.
TCP is supported when creating, altering, and dropping objects on DB2 V8 and above.
When creating, altering, or templating a table, you can manage its TCP as follows:
  • Convert a table using ICP to use TCP.
  • Convert a table using TCP to use ICP.
  • Manage partitioning key columns.
  • Manage partitions and their limit values.
  • Manage relationships between partition limit values.
Furthermore, if your table uses ICP, you can effortlessly have DDL automatically generated for you so that DB2 will implicitly convert your table so that it uses TCP instead of ICP by simply doing any one of the following things from the Table Alter screen (assuming no other changes are included that would require a drop and recreate of the table):
  • Change the Partitioning option from NO to YES.
  • Enter the KEY command on the primary command line, causing the Table Partitioning Key Col Selection & Maint screen to display, and then enter CANCEL (or END without making any changes to the table's partitioning key.)
  • Enter the LIMITS command on the primary command line, causing the Table Partitioning & Limit Key Values screen to display, and then enter CANCEL (or END without making changes to the table's partitioning that would cause a reduction in its original count of partitions.)