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

Operations are allowed to continue for views because creating and dropping views do not change
the structure of the dependent tables or the dependent table’s indexes. This contrasts with NonStop
SQL/MP, for which structure information is stored about views in the dependent table’s label.
Structure changing operations are sometimes allowed to run and succeed when inactive DDL locks
are present. Examples include:
• Dropping a table with inactive DDL lock
• Dropping an index with an inactive DDL lock
• Dropping a table that has a dependent index with an inactive DDL lock
An attempt to drop a table with an inactive DDL lock can fail if an inactive DDL lock exists on
another table that has a dependency on the first table. For example, Step 5 in this sequence would
fail and generate a DDL lock error:
1. Create table tab1.
2. Create table tab2.
3. Create an RI constraint on tab2 that references tab1.
4. Run a utility request on tab2 that fails and causes an inactive DDL lock to remain.
5. Attempt to drop table tab1.
Finally, certain ALTER operations are allowed to run and complete successfully when inactive DDL
locks are present. These ALTER operations change attributes in the labels but do not alter the
structure of the object or change its redefinition timestamp:
• ALLOCATE and DEALLOCATE option
• 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, 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” (page 188).
• Adding, moving, and dropping partitions for hash-partitioned tables and indexes. See “Using
MODIFY to Manage Hash-Partitioned Tables and Indexes” (page 191).
• Managing system-clustered tables and indexes. See “Managing System-Clustered Tables and
Indexes” (page 193).
For more information, 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
184 Reorganizing SQL/MX Tables and Maintaining Data