SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

Example of Dropping an Existing Empty Range Partition
Suppose that your company has archived all the orders from 2003 and earlier and no longer
needs to keep them in the ORDERSR table. You have deleted all records with ORDERTIMEs in 2003
and earlier from the table. As a result, the index partition $DATA01 is empty. Use this MODIFY
command to drop this empty index partition:
MODIFY INDEX cat2.sch2.ordersr_via_ordertime
drop partition
where first partition;
In this example, you use the syntax first partition instead of specifying a key value to refer
to the first index partition.
Example of Moving an Existing Partition to a New Location
You 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;
In response to this command, a portion of the data in each of the original three partitions is moved
automatically to the fourth partition, rebalancing the data evenly across the four partitions.
Using MODIFY to Manage Table and Index Partitions 191