SELECT FOR UPDATE

A lot of BDL programs use pessimistic locking in order to prevent several users editing the same rows at the same time.

DECLARE cc CURSOR FOR
SELECT ... FROM tab WHERE ... FOR UPDATE
OPEN cc
FETCH cc <-- lock is acquired
...
CLOSE cc <-- lock is released

In both Informix^® and DB2^®, locks are released when closing the cursor or when the transaction ends; DB2's locking granularity is at the row level.

To control the behavior of the program when locking rows:

Informix provides a specific instruction to set the wait mode:
SET LOCK MODE TO { WAIT | NOT WAIT | WAIT seconds }

The default mode is NOT WAIT. This as an Informix-specific SQL statement.
DB2 has no equivalent for "SET LOCK MODE TO NOT WAIT". The "Lock timeout" can be changed but this is a database parameter ( global to all processes )!

Solution

The database interface is based on an emulation of an Informix engine using transaction logging. Therefore, opening a SELECT ... FOR UPDATE cursor declared outside a transaction will raise an SQL error -255 (not in transaction).

You must review the program logic if you use pessimistic locking because it is based on the NOT WAIT mode which is not supported by IBM^® DB2.