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

a Flag Is Set in a Partition” (page 185)) and perform a FUP STATUS on the partition, using its
Guardian name, to determine whether or not the reload process is running:
◦ If the reload operation is still running, wait until it completes.
◦ If the reload operation is not running, perform a FUP RELOAD on the partition that has
the UNRECLAIMEDSPACE or INCOMPLETE SQLDDL OPERATION flag set.
• If the query returns a process ID for the MXCMP process associated with the MODIFY operation,
run the TACL command STATUS process-name to determine the current state of the process
ID (running or not running) and the type of the DDL lock returned by the query (active or
inactive).
◦ If the STATUS process-name command shows that the process is still running, the
returned lock information is for an active DDL lock. Wait until the MODIFY operation
completes. If the MODIFY operation starts an ORSERV process to reset the
UNRECLAIMEDSPACE or INCOMPLETE SQLDDL OPERATION flag, wait until the reload
operation completes.
◦ If the STATUS process-name command returns no process information, the returned
lock information is for an inactive DDL lock. Run the RECOVER command on the table or
index associated with the MODIFY request to cancel or resume the operation. The
RECOVER command can start an ORSERV process that resets either flag.
Using MODIFY to Manage Range-Partitioned Tables and Indexes
Use MODIFY to manage range-partitioned tables and indexes. You must manage tables and
indexes separately, regardless of their relationship. Both offline and online operations are supported.
MODIFY supports online partition management for range-partitioned tables and indexes where
the partitioning key is a prefix of the clustering key.
MODIFY supports these partition management operations for range partitions:
• Adding a new empty partition. You can add range partitions online. You can add one range
partition at a time to a hash-partitioned table, but you must do this offline.
• Dropping an existing empty partition.
• Moving an existing partition to a new location.
• Splitting an existing partition and then moving the first or last part of the data to a new partition.
From NonStop SQL/MX, splitting a single partition into two partitions requires two steps:
splitting off the first half of the partition and then moving the second half to a new partition.
• Splitting an existing partition and then merging the first or last part of the data to an existing
adjacent partition.
• Merging two adjacent partitions into one.
• Reusing an existing partition by setting the FIRST KEY values of the partition to new values.
You can optionally remove existing data in the partition to be reused.
Examples of Using MODIFY With Range-Partitioned Tables
For these range-partitioned table examples, suppose that the table ORDERSR has been created in
this way:
CREATE TABLE cat2.sch2.ordersr
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp,
primary key (location, ordernumber) not droppable)
location $DATA01
partition(
186 Reorganizing SQL/MX Tables and Maintaining Data