SQL/MX 2.x Reference Manual (H06.10+, J06.03+)

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—544517-008
5-109
Considerations for POPULATE INDEX
COMMIT [WORK] [ WHEN READY ] [on-error]
[ { AFTER time } ]
[ { BEFORE time } ]
specifies the time at which the Commit Phase should occur. COMMIT WHEN
READY specifies that the Commit Phase should occur at the earliest possible time.
COMMIT AFTER time specifies that the Commit Phase should occur after the
given time. COMMIT BEFORE time specifies that the Commit Phase should
occur before the given time. The on-error clause specifies what should happen
if the Commit Phase fails with a retryable error. If omitted, the effect is the same
as specifying ONCOMMITERROR ROLLBACK WORK. The time is a Datetime
value.
ROLLBACK WORK
specifies that the operation should be terminated. ROLLBACK WORK is specified
in an on-error clause.
ONCOMMITERROR commit-options
specifies what action SQL should take if a retryable error occurs during the commit
phase. Retryable errors include file in use, lock request time-outs, resource
unavailability, and missing a BEFORE or AFTER time window.
A non-retryable error always causes SQL to cancel changes to the database and
terminates the operation, no matter what you specify in the ONCOMMITERROR
option. ONCOMMITERROR is recursive because it appears within a COMMIT
option and specifies another COMMIT option. You can specify up to three COMMIT
options on a single statement. If you do not specify the ONCOMMITERROR
clause, the effect is the same as specifying ONCOMMITERROR ROLLBACK
WORK.
Considerations for POPULATE INDEX
•
To populate an index, you must be the super ID or the schema owner.
•
When POPULATE INDEX is being executed with shared access, the base table is
accessible for read write DML operations during that time period, except during the
commit phase at the end.
•
When POPULATE INDEX is being executed without shared access base table is
locked till populate index completes.
•
During an online POPULATE INDEX utility request, you can control when to
commit the transaction by giving a specific time. Specify an AFTER clause or a
BEFORE clause to control when to make the index available.
•
Retryable errors are errors that occur during any phase of the execution online
POPULATE INDEX. Retryable errors include file in use, lock request time-outs,