Migrating data from existing unpartitioned tables to partitioned tables

You can use the ALTER TABLE statement to migrate data of an unpartitioned table to a partitioned table. The ALTER TABLE statement for table partitioning functions similarly as the CREATE TABLE statement, except that the ALTER TABLE statement creates only the partition definition and points them to the current table partition (composite initial partition).

Note: The composite initial partition contains the data of a table before it is partitioned and all the partition-aligned indexes specified in the USING INDEX clause are known as composite indexes; Partition-aligned indexes are indexes that use partitioned key columns as the leading prefix components.

Data access from the new partition definition is automatically redirected to the composite initial partition, which is then gradually split into new actual partitions using a database utility.

Since all the data resides in the composite partition during the initial migration, all the partitions must be either read-only partitions or read-write partitions. If all the partitions are marked as read-only partitions, then the composite partition is marked as a read-only partition. If all the partitions are marked as read-write partitions, then the composite partition is marked as a read-write partition. An error occurs if a composite partition is created with both read-only partitions and read-write partitions.
Note: A NO SPACE partition cannot be marked as a read-only partition.

Migrating data of an unpartitioned table to a partitioned table involves the following steps:

  1. Enabling a table for partitioning
  2. Marking partitions as split targets
  3. Moving the physical data from the composite initial partition to its respective partitions
Note: If the table does not contain any data (if the number of rows in the table is zero), then this first step is sufficient to migrate data from an unpartitioned table to a partitioned table. This first step creates all the necessary partitions thereby, avoiding the need to prepare split targets and executing the database utility.

Examples

Marking a composite partition as a read-only partition while migrating data of an unpartitioned table to a partitioned table 

ALTER TABLE Pub.tpcustomer PARTITION BY LIST CustID 
USING TABLE AREA "Tenant_1_table_Area"
(
    PARTITION tpcustomer_PRTN1 VALUES IN (1001) READ_ONLY,
    PARTITION tpcustomer_PRTN2 VALUES IN (1002) READ_ONLY,
    PARTITION tpcustomer_PRTN3 VALUES IN (1003) READ_ONLY,
    PARTITION tpcustomer_PRTN3 VALUES IN (1004) READ_ONLY,
)
    USING INDEX tpcustomer_IDX3;

Marking a composite partition as a read-write partition while migrating data of an unpartitioned table to a partitioned table 

ALTER TABLE Pub.tpcustomer PARTITION BY LIST CustID
USING TABLE AREA "Tenant_1_table_Area"
(
    PARTITION tpcustomer_PRTN1 VALUES IN (1001) READ_WRITE,
    PARTITION tpcustomer_PRTN2 VALUES IN (1002) READ_WRITE,
    PARTITION tpcustomer_PRTN3 VALUES IN (1003) READ_WRITE,
    PARTITION tpcustomer_PRTN3 VALUES IN (1004) READ_WRITE,
)
    USING INDEX tpcustomer_IDX3;
Note: An error occurs if the composite partition is created with both read-only partitions and read-write partitions.

Marking a composite partition as a read-only partition

The following example illustrates an alternative syntax for marking a composite partition as a read-only partition:

ALTER TABLE Pub.tpcustomer SET READ_ONLY FOR PARTITION PRO_INITIAL;

Marking a composite partition as a read-write partition

The following example illustrates an alternative syntax for marking a composite partition as a read-write partition:

ALTER TABLE Pub.tpcustomer SET READ_WRITE FOR PARTITION PRO_INITIAL;