The Teradata SQL BEGIN TRANSACTION and END TRANSACTION statements define the beginning and end of a database transaction.
Either all or none of the statements in a transaction delimited by the BEGIN/END TRANSACTION statements are committed.
If any of the statements in a transaction fails to execute, the changes are backed out and the database returns to the condition prior to issuing the BEGIN TRANSACTION statement.
If a request to update data is made to the database after a BEGIN TRANSACTION is issued and the CICS transaction fails, the update is automatically backed out.
This recovery occurs because the session is logged off before an END TRANSACTION statement is encountered and the transaction is not logically complete.
CICS backs out the resources that were specified for recovery and rolls back any changes to the database.
The database treats a single request, which may consist of multiple statements, such as UPDATE, as a transaction unless it is located within a matching pair of BEGIN TRANSACTION/END TRANSACTION statements.
A macro that contains several statements is also treated as a single database transaction.
A matching BEGIN TRANSACTION/END TRANSACTION pair embedded within another such pair is equally acceptable.
Every BEGIN TRANSACTION statement, however, must correspond to an END TRANSACTION statement.
- A BEGIN TRANSACTION should be issued ahead of any Teradata SQL statement that performs an update.
- The END TRANSACTION statement should be issued immediately before the CICS RETURN command.
- An END TRANSACTION must be issued immediately following the CICS SYNCPOINT command. This command ensures that both CICS and the database roll back their resources if the program abends during syncpoint processing.
- Do not allow an END TRANSACTION statement to appear before the SYNCPOINT statement. The database will commit its changes, but then cannot roll them back if the SYNCPOINT fails.
- A time window exists after the CICS SYNCPOINT command finishes and END TRANSACTION statement processing begins. If a database failure occurs during this period, CICS and database resources may not be synchronized.
- The ABORT request may be sent either before or after the EXEC CICS SYNCPOINT ROLLBACK command. If CICS fails during rollback processing, an ABORT request is sent automatically to the database.
- The ABORT request may be sent to the database before the EXEC CICS ABEND command. An ABORT request is sent automatically to the database if the CICS transaction abends.
- If BEGIN TRANSACTION has been sent and the CICS transaction ends before an END TRANSACTION successfully finishes, the database will roll back the changes.
- If BEGIN TRANSACTION has been sent and LOGOFF occurs with END TRANSACTION, the database transaction is rolled back.
If a failure such as a program abend or CICS abend occurs, the host sends the database an ABORT request for its current transaction. All sessions associated with CICS automatically log off.
A database session may continue across CICS pseudo-conversational task end, but CICS forces a sync point for its resources at task end.