Transactions handling

Informix^® and IBM^® DB2^® handle transactions differently. The differences in the transactional models can affect the program logic.

Informix native mode (non ANSI):
- DDL statements can be executed (and canceled) in transactions.
- Transactions must be started with BEGIN WORK. Statements executed outside of a transaction are automatically committed.
IBM DB2:
- DDL statements can be executed (and canceled) in transactions.
- Beginning of transactions are implicit; two transactions are delimited by COMMIT or ROLLBACK.

Transactions in stored procedures:

Avoid using transactions in stored procedures to allow the client applications to handle transactions, in accordance with the transaction model.

Savepoints:

Informix version 11.50 introduces savepoints with the following instructions:

    SAVEPOINT name [UNIQUE]
    ROLLBACK [WORK] TO SAVEPOINT [name] ]
    RELEASE SAVEPOINT name

IBM DB2 supports savepoints too. However, there are differences:

Savepoints must be declared with the ON ROLLBACK RETAIN CURSORS clause
Rollback must always specify the savepoint name

Solution

The Informix behavior is simulated with an autocommit mode in the IBM DB2 interface. A switch to the explicit commit mode is done when a BEGIN WORK is performed by the BDL program. Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with IBM DB2.

Note: If you want to use savepoints, always specify the savepoint name in ROLLBACK TO SAVEPOINT.