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

SAP ASE

  • SAP® ASE supports two transaction modes:
    1. The SQL standards-compatible mode, called chained mode, to get implicit transaction.
    2. The default mode, called unchained mode, where transactions have to be started/ended explicitly.
  • Transactions are started with "BEGIN TRANSACTION [name]".
  • Transactions are validated with "COMMIT TRANSACTION [name]".
  • Transactions are canceled with "ROLLBACK TRANSACTION [name]".
  • Transactions save points can be placed with "SAVEPOINT [name]".
  • SAP ASE supports named and nested transactions.
  • DDL statements can be executed in transactions blocks when the 'ddl in tran' option is set to true with:
    master..sp_dboption dbname, 'ddl in tran', true
    go
    checkpoint
    go 

Solution

Informix transaction handling commands are automatically converted to SAP ASE instructions to start, commit or rollback transactions.

Make sure that the database uses the default unchained mode (set chained off) and allows DDLs in transactions ('ddl in tran' option is true).

Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with SAP ASE.