Transactions handling

Informix®

With the Informix native mode (non ANSI):

Transactions blocks start with BEGIN WORK and terminate with COMMIT WORK or ROLLBACK WORK.
Statements executed outside a transaction are automatically committed.
DDL statements can be executed (and canceled) in transactions.

UPDATE tab1 SET ...   -- auto-committed
BEGIN WORK            -- start of TX block
UPDATE tab1 SET ...
UPDATE tab2 SET ...
...
COMMIT WORK           -- end of TX block

Informix version 11.50 introduces savepoints:

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

IBM® DB2®

Transactions in IBM DB2:

Beginning of transactions are implicit; two transactions are delimited by COMMIT or ROLLBACK.
DDL statements can be executed (and canceled) in transactions.

Savepoints in IBM DB2:

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.