SQL/MX 2.x Installation and Management Guide (H06.04+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—540436-001
10-15
Using MODIFY to Manage Range-Partitioned Tables
and Indexes
process to reset the UNRECLAIMEDSPACE or INCOMPLETE SQLDDL
OPERATION flag, wait until the reload operation completes.
°
If the STATUS process-name command returns no process information, the
returned lock information is for an inactive DDL lock. Run the RECOVER
command on the table or index associated with the MODIFY request to cancel
or resume the operation. The RECOVER command can start an ORSERV
process that resets either flag.
Using MODIFY to Manage Range-Partitioned Tables and Indexes
Use MODIFY to manage range-partitioned tables and indexes. You must manage
tables and indexes separately, regardless of their relationship. Both offline and online
operations are supported.
MODIFY supports online partition management for range-partitioned tables and
indexes where the partitioning key is a prefix of the clustering key.
MODIFY supports these partition management operations for range partitions:
•
Adding a new empty partition. You can add range partitions online. You can add
one range partition at a time to a hash-partitioned table, but you must do this
offline.
•
Dropping an existing empty partition.
•
Moving an existing partition to a new location.
•
Splitting an existing partition and then moving the first or last part of the data to a
new partition. From NonStop SQL/MX, splitting a single partition into two partitions
requires two steps: splitting off the first half of the partition and then moving the
second half to a new partition.
•
Splitting an existing partition and then merging the first or last part of the data to an
existing adjacent partition.
•
Merging two adjacent partitions into one.
•
Reusing an existing partition by setting the FIRST KEY values of the partition to
new values. You can optionally remove existing data in the partition to be reused.
Examples of Using MODIFY With Range-Partitioned Tables
For these range-partitioned table examples, suppose that the table ORDERSR has
been created in this way:
CREATE TABLE cat2.sch2.ordersr
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp,
primary key (location, ordernumber) not droppable)
location $DATA01
partition(