Marking partitions as split targets

The following ALTER TABLE split partition syntax prepares an existing partition to be split into one or more partitions.

Syntax

ALTER TABLE table_name 
 [PREPARE FOR SPLIT {partition name| PRO_INITIAL }
  [{range_partition_split|list_partition_split]]
   <all other ALTER TABLE options>

Parameters

partition name

Refers to regular, non-composite RANGE partition name to be split. It reports an error if a LIST partition name is specified.

PRO_INITIAL

Refers to the composite initial partition of the migrated table.

range_partition_split

Uses the following syntax:

TARGET{PARTITION|PARTITIONS [partition name ] [ RO_RW_ATTRIBUTE ]area spec[, ...]

column_values

Uses the following syntax:

column_value [ , column_value, ...]

column_value

Uses a constant as its value. Each column_value corresponds to a column name of a partition or a subpartition, in the same order in which the column names are defined.

RO_RW_ATTRIBUTE

Uses the following syntax and marks the partition as a read-only partition or a read-write partition:

READ_ONLY | READ_WRITE

If RO_RW_Attribute is not specified, then by default, the partition is marked as a read-write partition.

list_partition_split

Uses the following syntax:

TARGET {PARTITION | PARTITIONS } partition_name [ RO RW ATTRIBUTE ][, ...]

RO_RW_ATTRIBUTE

Uses the following syntax and marks the partition as a read-only partition or a read-write partition:

READ_ONLY | READ_WRITE

If RO_RW_Attribute is not specified, then by default, the partition is marked as a read-write partition.

ALTER TABLE table_name PREPARE FOR SPLIT PRO_INITIAL;

Notes

The following semantics apply when using ALTER TABLE split initial partition (PRO_INITIAL) syntax:

• The table specified in the syntax must be a partitioned table
• While executing this command to split an initial composite partition (PRO_INITIAL), the table specified must be a migrated table.
• Split PRO_INITIAL marks all partitions referring to initial composite partition as split targets.
• The partition specified in the syntax cannot be accessed after execution, until the data is moved using the PROUTIL utility. However, it can be accessed by queries through the global index scan operation.
• The execution of this syntax creates the necessary target storage objects and their respective index partitions.
• In case of RANGE partitions, only one partition definition refers to the composite initial partition per a set of same leading LIST partition keys.
• All partition definitions referring to the composite initial partition are marked as split targets when the initial partition is split without new partition definitions (when neither range_partition_split nor list_partition_split is specified).
• The following semantics apply using ALTER TABLE split partition syntax where the initial partition is split with new partition definitions, with range_partition_split specified:
  • All the new partitions added are marked as split targets
  • The command can be used only with RANGE partitions
  • The new partition definitions must be within the boundaries of a composite partition
  • If the split source specified is a non-composite RANGE partition, then the source partition name must be specified using the PARTITION option of split utility.
• The following semantics apply using ALTER TABLE split partition syntax where the initial partition is split with specified target partitions, with list_partition_split specified:
  • All the partitions specified in the clause are marked as split targets.
  • If the partitions specified in the clause are already marked as split targets, the SQL engine returns an error.
  • Data in the specified partitions, once marked as split targets, is not accessible. It can be accessed from all other partitions that are not marked as split targets.
  • PARTITION and PARTITIONS clauses can be used interchangeably.
  • Execution of the above syntax results in an error if the partition that is marked as the split target is accessed unless the data is moved using the split utility.
  • Execution of the above syntax creates necessary storage objects and the respective index partitions for target partitions.

Examples

Marking partitions as read-only and read-write partitions while splitting non-composite RANGE partitions

ALTER TABLE Pub.tpcustomer
PREPARE FOR SPLIT NoholdDKP15500
(
  PARTITION NoholdDKP10000 VALUES <= (0, 'DKP', 10000) READ_ONLY,
  PARTITION NoholdDKP11000 VALUES <= (0, 'DKP', 11000),
  PARTITION NoholdDKP12000 VALUES <= (0, 'DKP', 12000),
  PARTITION NoholdDKP13000 VALUES <= (0, 'DKP', 13000) READ_WRITE,
  PARTITION NoholdDKP14000 VALUES <= (0, 'DKP', 14000) READ_WRITE,
  PARTITION NoholdDKP15000 VALUES <= (0, 'DKP', 15000) READ_ONLY
);

Data movement from a read-only source partition to a read-write target partition is not allowed. In the above example, the source partition NoHoldDKP15500 is a read-only partition, and since some of the split targets are marked as read-write partitions, the PROUTIL utility returns an error during the data movement.

Marking partitions as read-only and read-write partitions while splitting composite partitions

ALTER TABLE Pub.tpcustomer
  PREPARE FOR SPLIT PRO_INITIAL TARGET PARTITIONS
   tpcustomer_partn1 READ_ONLY, 
   tpcustomer_partn2 READ_ONLY, 
   tpcustomer_partn4 READ_WRITE,
   tpcustomer_partn5 READ_ONLY;