SUBTRANS Statement (subtrans_statement) 
The SUBTRANS statement (subtrans_statement) divides a transaction into units known as subtransactions.
Syntax
<subtrans_statement> ::= SUBTRANS BEGIN | SUBTRANS END | SUBTRANS ROLLBACK
Explanation
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.
The sequence can, however, 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; i.e. it is no longer considered to be an open subtransaction.
SUBTRANS ROLLBACK
If a subtransaction is concluded with SUBTRANS ROLLBACK, all the 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.
Additional Information
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 else have no effect. To this end, a SUBTRANS BEGIN is issued initially. 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 the modifications performed by the subroutine.
The COMMIT statement and the ROLLBACK statement close any open subtransactions implicitly.
See also: