MODIFY...PARTITION...TO SPLIT
The MODIFY...PARTITION...TO SPLIT WITH PARTITION= statement splits a partition into two or more partitions.
This modify operation drops all secondary indexes and rebuilds persistent indexes.
Syntax:
MODIFY tablename PARTITION partition_name TO SPLIT WITH PARTITION=(dimension)
where:
tablename
Specifies a range-partitioned table
partition_name
Specifies a logical partition name
dimension
Specifies the new partitions to replace the specified partition. For the syntax of PARTITION= specification, see
Partitioning Schemes.
The distribution type (dimension rule) must be RANGE.
For example, given the following table:
CREATE TABLE tbl ( aa INT, bb INT, cc INT)
WITH PARTITION=
( (RANGE ON aa
PARTITION aa1 VALUES <= 30, PARTITION aa2 VALUES > 30)
SUBPARTITION (RANGE ON bb
PARTITION bb1 VALUES <=20,
PARTITION bb2 VALUES <=40,
PARTITION bb3 VALUES >40)
SUBPARTITION (RANGE ON cc
PARTITION cc1 VALUES <=15,
PARTITION cc2 VALUES <=30,
PARTITION cc3 VALUES > 30))
MODIFY tbl PARTITION bb1 TO SPLIT WITH PARTITION=
(RANGE ON bb
PARTITION bb1a VALUES <= 10,
PARTITION bb1b VALUES <= 20)
Note: Logical partition names can be found in iilpartitions.partition_name in iidbdb database by running the following query:
SELECT partition_name, operator, value, dimension, logical_partseq FROM iilpartitions WHERE tablename='tablename' ORDER BY dimension, logical_partseq
Only the logical partitions listed will be processed. The replacement partition definition must exactly match the range defined by those logical partitions. The MODIFY will change the number of partitions and how the data is divided among the new partitions.
If the partitioning definition is multi-dimensional (that is, defined with subpartitions), you can split or merge or drop partitions in any dimension. All logical partitions specified, however, must be in a single dimension.
If you use a WITH LOCATION clause, the partition (and its sub-partitions) are created in the locations specified in the WITH LOCATION clause. If you do not use a WITH LOCATION clause, the partition (and its sub-partitions) are created in the table locations.
Last modified date: 08/29/2024