SUBTRANS BEGIN

A subtransaction is opened. This means the database system records the current point in the transaction. Any sequence of SQL statements can then follow. If the sequence does not contain an additional SUBTRANS BEGIN, all database modifications performed since the SUBTRANS BEGIN can be reversed using a SUBTRANS ROLLBACK.

However, the sequence can also contain additional SUBTRANS BEGIN statements that open additional subtransactions. This means several nested subtransactions may be opened at the same time.

SUBTRANS END

If a subtransaction is concluded with SUBTRANS END, the modifications that have been made are kept. A subtransaction is closed. This means the database system "forgets" the point in the transaction recorded with SUBTRANS BEGIN. A prerequisite for this is that an open subtransaction exists. If more than one open subtransaction exists, the last opened subtransaction is closed; that is, it is no longer considered to be an open subtransaction.

SUBTRANS ROLLBACK

If a subtransaction is concluded with SUBTRANS ROLLBACK, all database modifications made within a subtransaction are reversed and the subtransaction is closed. Any database modifications performed by any subtransactions within the subtransaction are reversed, irrespective of whether they were ended with SUBTRANS END or SUBTRANS ROLLBACK. All result tables generated within the subtransaction are closed.

A prerequisite for this is that an open subtransaction exists. If more than one open subtransaction exists, the last opened subtransaction is rolled back. The subtransaction concerned is then no longer considered open.

Further Explanations

The SUBTRANS statement does not affect locks assigned to the transaction. In particular, SUBTRANS END and SUBTRANS ROLLBACK do not release any locks.

The SUBTRANS statement is particularly useful for keeping the effects of subroutines or database procedures atomic in the sense that they either fulfill all their tasks or otherwise have no effect. A SUBTRANS BEGIN statement is issued initially for this. If the subroutine succeeds in fulfilling its task, it is ended with a SUBTRANS END. In the event of an error, a SUBTRANS ROLLBACK is used to reverse all modifications performed by the subroutine.

The COMMIT statement and the ROLLBACK statement close any open subtransactions implicitly.