SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—523723-004
10-21
Managing System-Clustered Tables and Indexes
Example of Dropping an Existing Hash Partition and Rebalancing Data
Orders suddenly decline, and you decide to free up some of the disk resources used
by the index. Use this MODIFY command to drop the fourth partition and redistribute
its data to the remaining partitions:
MODIFY INDEX cat2.sch2.ordersr_via_ordernumber
drop partition
where last partition;
By using the syntax last partition in the example, you avoid the need to specify
the last partition by its location.
Example of Moving an Existing Hash Partition to a New Location
You need to use $DATA03 for other purposes. Use the MODIFY command to move
the index partition there to another volume:
MODIFY INDEX cat2.sch2.ordersr_via_ordernumber
move
where location $data03
to location $data05;
Managing System-Clustered Tables and Indexes
A system-clustered table or index has no primary key and no STORE BY clause. Its
primary key defaults to the SYSKEY.
You can use MODIFY to move the existing partition of a system-clustered table or
index to a new location. A system-clustered table can have only a single partition. Only
offline partition operations are supported.
For system-clustered partitions, MODIFY supports moving an entire system-clustered
partition to a new location.
Using MODIFY in a System-Clustered Partition
For this system-clustered partition example, suppose that the table ORDERSSC has
been created in this way:
CREATE TABLE cat4.sch4.orderssc
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp)
location $DATA09;