SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-55
Considerations—COMMIT Option
ROLLBACK [WORK]
cancels changes made to the database during the operation and terminates the
operation.
Considerations—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 these
options:
... WITH SHARED ACCESS NAME OP1
COMMIT BY REQUEST ONCOMMITERROR COMMIT BY REQUEST;
and later continue the operation with this statement:
CONTINUE OP1 COMMIT;
the ONCOMMITERROR option for the final phase of the operation is the default,
ONCOMMITERROR ROLLBACK WORK.

For information on the phases of an operation that use the COMMIT options, see
WITH SHARED ACCESS OPTION on page W-4.
Example—COMMIT Option
This 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 does 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
beginning. (The output in the example indicates that the operation terminates without
error.)
>>ALTER TABLE $HR.PERSONEL.EMP MOVE TO $HDQ.PERSONEL.EMP
+> WITH SHARED ACCESS COMMIT BY REQUEST;
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.