DB2 Version 9.7 for Linux, UNIX, and Windows

Insensitive cursor

Starting with Version 9.7 Fix Pack 2 and later fix packs, you have the option of making cursors insensitive to subsequent statements by materializing the cursor at OPEN time.

For a cursor to be insensitive to other data change statements, the result set is materialized at OPEN time and the cursor behaves as a read only cursor. Without insensitive cursor support there is no guarantee that DB2 cursors will be materialized at OPEN time, which could cause different result sets when a query is run in DB2 as opposed to a relational database that immediately materializes cursors. For example, Sybase TSQL includes the capability of issuing a query from a batch or procedure code that produces a result set for the invoker. The query is materialized immediately and other statements in the block assume that they cannot impact 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 than the Sybase result.

Insensitive cursor support is enabled by setting bit position number 13 (0x1000) of the DB2_COMPATIBILITY_VECTOR registry variable. When this bit is set, all cursors defined as WITH RETURN are INSENSITIVE as long as they are not explicitly marked as FOR UPDATE. A new setting for the registry variable does not take effect until after the instance has been stopped and then restarted.

The DECLARE CURSOR statement is extended allowing a cursor to be defined as INSENSITIVE. Declaring an insensitive cursor is only supported in the context of a compound SQL (compiled) statement.

The STATICREADONLY option of the BIND command now allows a specification of INSENSITIVE. A package that is bound with STATICREADONLY INSENSITIVE will cause all read only and ambiguous cursors to be insensitive. This bind option will also be supported in the registry variable DB2_SQLROUTINE_PREPOPTS and the procedure SET_ROUTINE_OPTS, so that SQL routines can make all read only and ambiguous cursors issued as static SQL materialize at OPEN time.

Restrictions

The INSENSITIVE keyword can only be specified on a DECLARE CURSOR statement used within a compound SQL (compiled) statement. It is not supported by any of the precompilers. No changes are made to CLI or JDBC to identify 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 prior to starting the DELETE statement.

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