SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—523723-004
10-9
Using MODIFY to Manage Table and Index
Partitions
•
Creating or dropping views on table that has dependent indexes with active or
inactive locks
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 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: