Transaction processing
A transaction is a sequence of one or more SQL statements that together form a logical unit of work. A transaction automatically begins with the first SQL statement that is run within a client connection to the data server.
- A client COMMIT statement ends the transaction and makes any database changes permanent.
- A client ROLLBACK statement aborts the transaction and backs out any database changes.
- A client disconnect occurs that results in a transaction commit.
- An unexpected client disconnect occurs that results in a transaction rollback. In addition to committing or rolling back changes, all open cursors that are associated with the transaction are closed and any prepared statements are freed.
After a transaction is completed by a commit or a rollback, a new transaction starts with the next SQL statement that the SQL client issues.
Some clients support an autocommit mode of processing that makes each SQL statement its own transaction by automatically issuing an explicit COMMIT statement after each SQL statement. In applications that require bundling multiple updates in the same transaction, this feature must be deactivated.
Transaction processing depends on the capabilities of the underlying connectors. In some cases, the connector does not allow an update because the data source does not support a method of committing or rolling back database changes.
The CA-Datacom, DB2® for z/OS®, and IMS data sources support two-phase commit capabilities.
In general, it is good practice to issue a ROLLBACK statement when the data server returns a negative SQLCODE.