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

Note that the DROP PARTITON syntax is not used in this example because the partition $DATA03,
which contains ‘New York,’ is not yet empty.
Example of Reusing an Existing Range Partition by Setting the FIRST KEY Values to New Values
You have reorganized your business so that all orders that originate in ‘Chicago’ and all locations
with values greater than ‘Chicago’ are processed through an agent in ‘Boston.’ You have already
deleted all rows from the ORDERSR table with locations greater than or equal to the value of
‘Chicago,’ which empties the ‘Cincinnati’ partition, $DATA02. All new orders should go into the
$DATA00 partition. Consequently, you decide to drop the ‘Cincinnati’ partition and create a new
partition for ‘Boston’ orders. Use this MODIFY REUSE command to do this in one operation:
MODIFY TABLE cat2.sch2.ordersr reuse partition
where key = value ('Cincinnati')
with key = value ('Boston');
Examples of Using MODIFY With Range-Partitioned Indexes
These range-partitioned index examples are based on the range-partitioned table ORDERSR from
“Examples of Using MODIFY With Range-Partitioned Tables” (page 188). Suppose that it has become
necessary to group customer orders based on the time they were placed. Although you can perform
grouping against the base table directly, suppose that there is a requirement for direct access to
particular dates. One way to perform the grouping is to add this range-partitioned index on the
ORDERTIME column of ORDERSR:
CREATE INDEX ordersr_via_ordertime
ON cat2.sch2.ordersr (ordertime)
LOCATION $data01
PARTITION (
ADD FIRST KEY TIMESTAMP '2004-01-01 00:00:00.000000'
LOCATION $data02,
ADD FIRST KEY TIMESTAMP '2004-07-01 00:00:00.000000'
LOCATION $data03 );
The partition on $DATA01 contains index records for orders from 2003 and earlier, the partition
on $DATA02 contains index records for orders from the first half of 2004, and the partition on
$DATA03 contains index records for orders for the second half of 2004 and later.
Managing partitions on a range-partitioned index is quite similar to managing partitions on a
range-partitioned table. You can still perform ADD, DROP, MOVE, SPLIT, and MERGE operations.
The only table operation that cannot be performed on an index is REUSE. The next examples
illustrate these supported operations.
Example of Adding a New Range Partition
Business has exceeded expectations in the first half of 2004. The index partition on $DATA02 has
become too large, and you want to move May and June orders to a new partition, $DATA04. Use
this MODIFY command:
MODIFY INDEX cat2.sch2.ordersr_via_ordertime
add partition
where key =
value (timestamp '2004-05-01 00:00:00.000000')
to location $data04;
NOTE: In the MODIFY INDEX command, the catalog and schema names of the index follow the
usual default naming rules. In particular, if the catalog or schema name is not specified, the default
catalog or schema name from the session is used. In the CREATE INDEX command, however, the
catalog and schema names cannot be specified for the index name. Instead, the CREATE INDEX
command always creates the index in the same catalog and schema as the underlying base table.
190 Reorganizing SQL/MX Tables and Maintaining Data