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-10
Using MODIFY to Manage Table and Index
Partitions
•
AUDITCOMPRESS
•
CLEARONPURGE
•
MAXEXTENTS
DDL locks on indexes have their own set of considerations. ALTER operations are
often allowed on the parent table even if the index has a DDL lock because the change
to the parent table does not affect the structure of the dependent index.
For more information about DDL locks, see the SQL/MX Reference Manual.
Using MODIFY to Manage Table and Index
Partitions
As the data in SQL/MX tables and indexes is updated and removed and new data is
added, you need to manage table and index partitions. Use the MODIFY utility to
perform these partition management operations:
•
Adding, splitting, merging, moving, and dropping partitions for range-partitioned
tables and indexes. See Using MODIFY to Manage Range-Partitioned Tables and
Indexes on page 10-15.
•
Adding, moving, and dropping partitions for hash-partitioned tables and indexes.
See Using MODIFY to Manage Hash-Partitioned Tables and Indexes on
page 10-19.
•
Managing system-clustered tables and indexes. See Managing System-Clustered
Tables and Indexes on page 10-21.
For more information about the MODIFY utility, see the SQL/MX Reference Manual.
MODIFY and EMS Messages
MODIFY generates EMS messages to report operation status. If an error occurs during
a MODIFY operation, check the EMS log for possible information about where the
process failure occurred.
Online and Offline Partition Management
MODIFY can run as either an offline or online operation. You can perform online
operations while the partition is being used by another application. You can perform
offline operations only on partitions that are not being used by other applications or that
are being used with READ access. WRITE access is prohibited.
MODIFY’s with-shared-access option supports online partition management of
range-partitioned tables and indexes in which the partitioning key is a prefix of the
clustering key. This feature provides full read and write access to a range-partitioned
table or index for the duration of most operations. Other processes can read and write
the table or index while it is being repartitioned, except during a short period at the end
when file labels and metadata are updated.