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.

See also SELECT FOR UPDATE