SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

add first key ('Chicago') location $DATA02,
add first key ('New York') location $DATA03 );
Also suppose that the table ORDERSR contains orders with these locations:
• Atlanta
• Chicago
• Cincinnati
• New York
Example of Adding a New Range Partition
You expect several thousand orders for ‘Seattle’ to arrive shortly. As the table ORDERSR is currently
defined, you would insert these orders into the partition on $DATA03. But if $DATA03 is already
quite active, you might elect to split this load over another disk volume. This MODIFY command
splits the $DATA03 partition, moving all locations from ‘Seattle’ and beyond to a new partition
on $DATA04:
MODIFY TABLE cat2.sch2.ordersr add partition
where key = value ('Seattle')
to location $DATA04;
Example of Dropping an Existing Empty Range Partition
Contrary to expectations, no orders have been received from ‘Seattle,’ and no orders have been
received with a location value greater than or equal to ‘Seattle.’ You decide to get rid of the empty
partition that you just created on $DATA04. Use this MODIFY command to drop that partition:
MODIFY TABLE cat2.sch2.ordersr drop partition
where key = value ('Seattle');
After you issue this command, if new rows with a location value greater than or equal to ‘Seattle’
arrive, they are inserted into the partition on $DATA03.
Example of Moving an Existing Partition to a New Location
You decide to use $DATA01 for other purposes. You want to move the first partition of the table
to a different volume, $DATA00. Use this MODIFY TABLE command:
MODIFY TABLE cat2.sch2.ordersr move
where key = first partition to location $DATA00;
Example of Splitting an Existing Range Partition and Merging the First or Last Part to an Existing
Partition
Business has improved, and many new orders have been received from ‘Chicago’ and ‘Cincinnati.’
At the same time, very few orders have been received from ‘Atlanta.’ The result is that the partition
on $DATA02 is much more active than the partition on $DATA00. So you decide to rebalance
the load. You do this by moving the key range from ‘Chicago’ up to—but not including—‘Cincinnati’
to the $DATA00 partition. Use this MODIFY TABLE command:
MODIFY TABLE cat2.sch2.ordersr move
where key = first key upto key = value ('Cincinnati')
to previous partition;
Example of Merging Two Adjacent Range Partitions Into One Range Partition
You have sold your New York subsidiary to another company. As a result, ‘New York’ orders soon
begin to decline significantly, and there is not enough demand to justify retaining a separate
partition for ‘New York’ orders. You decide to merge the data from this partition into the partition
on $DATA02 and to drop the $DATA03 partition. Use this MODIFY TABLE command:
MODIFY TABLE cat2.sch2.ordersr move
where key = value ('New York') thru key = last key
to previous partition;
Using MODIFY to Manage Table and Index Partitions 187