DB2 Version 10.1 for Linux, UNIX, and Windows

Insensitive cursor

You can make cursors insensitive to subsequent statements by materializing the cursor at OPEN time. Statements that are executed while the cursor is open do not affect the result table once all the rows have been materialized in the temporary copy of the result table.

Enablement

You can enable insensitive cursors by setting the DB2_COMPATIBILITY_VECTOR registry variable to hexadecimal value 0x1000 (bit position 13), and then stop and restart the instance to have the new setting take effect.

db2set DB2_COMPATIBILITY_VECTOR=1000
db2stop
db2start

To take full advantage of the DB2 compatibility features for Oracle applications, the recommended setting for the DB2_COMPATIBILITY_VECTOR is ORA, which sets all of the compatibility bits.

When the result set is materialized at OPEN time, the cursor behaves as a read only cursor. All cursors defined as WITH RETURN are INSENSITIVE as long as they are not explicitly marked as FOR UPDATE. If you do not enable insensitive cursor support, there is no guarantee that DB2® cursors will be materialized at OPEN time. Therefore, the result sets that are generated when you run the same query against a DB2 database and a relational database that immediately materializes cursors might be different. For example, Sybase TSQL includes the capability of issuing a query from a batch statement or a procedure that produces a result set for the invoker. The query is materialized immediately. Other statements in the block expect that they cannot affect the result and issue statements, such as DELETE, against the same table that was referenced in the query. When a similar scenario is run without an insensitive cursor, the result set from that cursor will be different from the Sybase result.

Insensitive cursors can also be set in the following ways:

You can define a cursor as INSENSITIVE in a DECLARE CURSOR statement that is used in a compound SQL (compiled) statement.
If you bind a package with the STATICREADONLY INSENSITIVE parameter of the BIND command, all read-only and ambiguous cursors are insensitive.
If you specify the STATICREADONLY INSENSITIVE option for the DB2_SQLROUTINE_PREPOPTS registry variable or the SET_ROUTINE_OPTS procedure, at OPEN time, SQL routines materialize all-read only and ambiguous cursors that are issued as static SQL.

Restrictions

The INSENSITIVE keyword is not supported by any of the precompilers. CLI and JDBC do not provide support for identifying insensitive nonscrollable cursors (either cursor attributes or result set attributes).

Example

This code returns the entire result set of the SELECT statement to the client before executing the DELETE statement.

BEGIN
 DECLARE res INSENSITIVE CURSOR WITH RETURN TO CLIENT FOR
  SELECT * FROM T;
  OPEN T;
  DELETE FROM T;
END