Transactions handling
Informix®
With the Informix native mode (non ANSI):
- Transactions blocks start with
BEGIN WORK
and terminate withCOMMIT WORK
orROLLBACK 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
orROLLBACK
. - 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