SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—523725-004
5-70
Considerations for POPULATE INDEX
loaded, specify RECOVER with the CANCEL option to roll back the operation. If you
run the RECOVER operation with the incorrect option, RECOVER displays an
error message so you can rerun it with the correct option. For information, see
Checking File Locks on page 5-3.
No restart facility is provided to recover partially-loaded data.
•
To avoid TMF limitations if a large amount of data is to be moved, POPULATE
INDEX runs in multiple TMF transactions. The actual loading of the data is
performed outside a TMF transaction.
•
After you perform POPULATE INDEX, you should perform a FUP RELOAD on the
index and all its partitions, to organize the index structure more efficiently and to
reduce index levels.
•
POPULATE INDEX records operation progress steps in the DDL_LOCKS
metadata table. See the description of DDL_LOCKS under Considerations for DUP
on page 5-8. You can query this table to determine the POPULATE INDEX
operation’s progress:
•
Any process, CPU, or system failure that occurs causes the operation to be rolled
back. You can recover from a failure at any time by purging data from the index
and starting over.
•
An error is returned if a duplicate entry is found while loading a unique index.
•
An error is returned if a user transaction exists.
POPULATE
INDEX
Operation
Step Step Progress Status
Step 1 Base table has been opened.*
Step 2 DDL lock has been created.
Step 4** Index load has started.
Step 5 Index load has completed.
Step 6 Base table is locked.***
Step 7 Index is now online.
Step 8 Base table is closed.
Step 9 Base table is now unlocked.
Step 10 DDL lock has been removed.
*
Between Steps 1 and 5, you can perform read access but not DDL requests on the base table.
**
The DDL_LOCKS metadata table does not have a Step 3.
***
Between Steps 6 and 9, the base table is locked for exclusive access while the index is brought online.
During this period, read access is denied on the base table.