Cursors WITH HOLD

Programming WITH HOLD cursors using SELECT with and without FOR UPDATE clause.

Type of WITH HOLD cursors

Make sure to distinguish cursors declared WITH HOLD for regular SELECT statements, and WITH HOLD cursors declared for a SELECT statements using the FOR UPDATE clause.

Regular WITH HOLD cursors (without FOR UPDATE clause in SELECT)

Most database brands allow cursors to remain open across transactions, as long as the SELECT statement is not defined with a FOR UPDATE clause.
DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM customers

Therefore, a cursor declared WITH HOLD with a SELECT statement not using the FOR UPDATE clause can be used with most databases. However, some databases close any cursor (even declared without FOR UPDATE), when a transaction is rolled back, or when the cursor is opened inside the transaction block.

Table 1. Database server support of WITH HOLD cursors (with no FOR UPDATE clause)
Database Server Type WITH HOLD support
IBM® DB2® LUW Partial, see details
IBM Informix® Yes, native SQL feature
IBM Netezza No, see details
Microsoft™ SQL Server Yes, see details
Oracle® MySQL / MariadDB Yes, see details
Oracle Database Server Yes, see details
PostgreSQL Partial, see details
SAP® ASE Yes, see details
SAP HANA® Yes, see details
SQLite Yes, see details

Cursors WITH HOLD using SELECT FOR UPDATE

IBM Informix supports WITH HOLD cursors using the FOR UPDATE clause:
DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM customers FOR UDPATE

Such cursors can remain open across transactions (when using FOR UPDATE, locks are released at the end of a transaction, but the WITH HOLD cursor is not closed).

Not all database brands allow "for update" cursors to remain open across transactions: The SQL standards recommend closing FOR UPDATE cursors and release locks at the end of a transaction.

Most database servers close FOR UPDATE cursors when a COMMIT WORK or ROLLBACK WORK is done.

All database servers release locks when a transaction ends.

Table 2. Database server support of WITH HOLD FOR UPDATE
Database Server Type WITH HOLD FOR UPDATE support
IBM DB2 LUW No, see details
IBM Informix Yes, native SQL feature
IBM Netezza No, see details
Microsoft SQL Server No, see details
Oracle MySQL / MariadDB No, see details
Oracle Database Server No, see details
PostgreSQL No, see details
SAP ASE No, see details
SAP HANA No, see details
SQLite No, see details

It is mandatory to review code using WITH HOLD cursors with a SELECT statement having the FOR UPDATE clause.

The standard SQL solution is to declare a FOR UPDATE cursor (without WITH HOLD clause) outside the transaction and open the cursor inside the transaction:
DECLARE c1 CURSOR FOR SELECT ... FOR UPDATE
BEGIN WORK
  OPEN c1
  FETCH c1 INTO ...
  UPDATE ...
COMMIT WORK
If you need to process a large result set by doing updates of master and detail rows, first fetch the primary keys of all master rows into a program array, declare a cursor with the SELECT FOR UPDATE, for each row in the array, start a transaction and perform the UPDATE WHERE CURRENT OF for the current master record and the UPDATE for detail rows, then commit the transaction and continue with the next master record:
DEFINE x, mkeys DYNAMIC ARRAY OF INTEGER
DECLARE c1 CURSOR FOR SELECT key FROM master ...
FOREACH c1 INTO x
    LET mkeys[mkeys.getLength()+1] = x
END FOREACH
DECLARE c2 CURSOR FOR SELECT * FROM master WHERE key=? FOR UPDATE
FOR x = 1 TO mkeys.getLength()
  BEGIN WORK
  OPEN c2 USING mkeys[x]
  FETCH c2 INTO mrec.*
  IF STATUS==NOTFOUND THEN
     ROLLBACK WORK
     CONTINUE FOREACH
  END IF
  UPDATE master SET ... WHERE CURRENT OF c2
  UPDATE detail SET ... WHERE master_key=mkeys[x]
  COMMIT WORK
END FOR
Note: When the underlying database table is defined with a primary key column, consider replacing the WHERE CURRENT OF clause by WHERE pkey-column = ?, which is a more portable syntax. With some types of databases, the WHERE CURRENT OF clause has to be emulated, or is simply unsupported. For more details, see Positioned UPDATE/DELETE.