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-19
Using MODIFY to Manage Hash-Partitioned Tables
and Indexes
Example of Moving an Existing Partition to a New Location
You suddenly need to use $DATA04 for other purposes. Use this MODIFY command
to move the index partition on $DATA04 to another volume:
MODIFY INDEX cat2.sch2.ordersr_via_ordertime
move
where key =
value (timestamp '2004-05-01 00:00:00.000000')
to location $data05;
Using MODIFY to Manage Hash-Partitioned Tables and Indexes
Use MODIFY to manage hash partitions of SQL/MX tables and indexes. You can drop
only the last partition. You must manage tables and indexes separately, regardless of
whether or not they are related.
You can perform only offline partition management operations for hash-partitioned
tables and indexes. MODIFY supports these offline partition management operations
for hash partitions:
•
Adding a new hash partition and rebalancing data (that is, redistributing existing
data to all partitions, including the new partition).
•
Dropping the last existing hash partition and rebalancing data.
•
Moving an existing hash partition to a new location.
Examples of Using MODIFY With Hash-Partitioned Tables
For these hash-partitioned index examples, suppose that the table ORDERSH has
been created in this way:
CREATE TABLE cat3.sch3.ordersh
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp,
primary key (location, ordernumber) not droppable)
location $DATA01
hash partition(
add location $DATA02,
add location $DATA03);
Example of Adding a New Hash Partition and Rebalancing Data
The table ORDERSH is partitioned three ways. When business orders increase, you
decide to rebalance the orders data over four disks. Use this MODIFY TABLE
command to add the fourth partition:
MODIFY TABLE cat3.sch3.ordersh add partition
location $DATA04;