NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-50
Examples—COMMIT Option
Each COMMIT option completely replaces the previous one in effect for the
operation. For example, if you execute a DDL statement that includes the following
options:
... WITH SHARED ACCESS NAME OP1
COMMIT BY REQUEST ONCOMMITERROR COMMIT BY REQUEST;
and later continue the operation with the following statement:
CONTINUE OP1 COMMIT;
the ONCOMMITERROR option for the final phase of the operation is the default,
ONCOMMITERROR ROLLBACK WORK.
•
Phases of operations that include the commit option
See WITH SHARED ACCESS OPTION
on page W-4 for a detailed discussion of
the phases of an operation that uses the a commit option.
Examples—COMMIT Option
The following example shows an ALTER TABLE operation begun from SQLCI with a
COMMIT BY REQUEST option. When the operation is ready to begin its final phase
(possibly long after the user enters the initial ALTER TABLE statement), the user issues
a CONTINUE statement that includes different COMMIT options.
COMMIT AFTER 02:00 specifies a start time of 2:00 am, when there is unlikely to be
significant activity on the objects involved. TIMEOUT NEVER directs the operation to
wait indefinitely for its lock requests to complete.
The CONTINUE statement also specifies ONCOMMITERROR COMMIT BY
REQUEST so that SQL will not automatically roll back the operation if a retryable error
occurs during the final phase. This specification gives the user an opportunity to fix
problems that cause the error and continue the operation without restarting from the
Note. You should normally specify ONCOMMITERROR COMMIT BY REQUEST on the
last commit option you specify for an operation, because that protects the operation from
automatically being rolled back if a retryable error occurs.