EXEC SQL DECLARE SPJSC CURSOR 
  SENSITIVE STATIC SCROLL FOR
  SELECT SN, PN, JN, QTY
  FROM   SPJ
  WHERE  JN = :JN
  ORDER BY SN, PN, JN
END-EXEC.

EXEC SQL OPEN SPJSC END-EXEC.

EXEC SQL FETCH SPJSC INTO :SN, :PN, :JN, :QTY END-EXEC.

A claim is held on the base tablespace with a SENSITIVE cursor (discussed in the next subsection). This means that online reorganization cannot complete until it can complete draining claims. This requirement is due to the fact that the RIDs in the temporary results table do not point to the correct rows after a reorganization.

Column functions are computed when the cursor is opened and the results are not changed when data is changed. For example:

The average QTY is computed when the cursor is opened. It is not changed as rows are inserted, updated, and deleted while processing the scrollable cursor.

The CLOSE statement frees the space that the temporary results table occupied.

Sensitivity of Scrollable Declare Cursor Statement

SENSITIVE STATIC means that rows in the base table are always seen even if they differ from the temporary results table rows due to updates and deletes on the base table. Inserted rows are not seen. This is accomplished by re-evaluating a fetched row against predicates of the cursor by DB2. The row is refreshed from the base table by locating the row using the RID placed in the temporary results table. An updated row is not seen if it no longer matches the predicate and a deleted row is not seen at all.

SENSITIVE DYNAMIC may be seen to indicate that a temporary results table is not used. Scrollable cursors can be implemented using bidirectional indexes that allow the use of an ascending index to avoid a descending sort, (and the opposite for access to the base table), without the use of a temporary results table.

INSENSITIVE STATIC means that only rows in the temporary results table are seen when rows are fetched again. New values in the base table as a result of an update and insert are not seen. Deleted rows in the base table rows are seen.

SQLWARN4 in the SQLCA indicates if the cursor is sensitive or insensitive and SQLWARN5 indicates if the cursor is read only or updatable.

The DB2 implementation of scrollable cursors is a subset of SQL99 standards which include DYNAMIC and ASENSITIVE.

There is no default sensitivity at present.

Sensitivity of Fetch Statement

By default, the sensitivity of a FETCH statement is the same as its declare cursor statement. If the declare cursor is defined with SENSITIVE, you can specify FETCH INSENSITIVE. However, if the declare cursor is defined with INSENSITIVE, you cannot specify FETCH SENSITIVE.

If the executing program changes data using the cursor (positioned update or delete, for example), the change is visible to the executing program before and after a commit. This is accomplished by applying the change to the temporary result table. 

If the executing program or another program changes the data outside of the cursor, changes are seen only when using FETCH SENSITIVE. This includes before and after commit of the executing program. Another program must issue a commit for its change to be seen. Only the fetched row is refreshed.

Fetching from a Scrollable Cursor

The FETCH statement provides for requesting:

The n in ABSOLUTE and RELATIVE can be a literal or host variable defined as INTEGER or DECIMAL(n,0).

Assume that five screens with four rows each have been displayed to the user as shown in Figure 1 with a '1' in an arrow pointing to the current position. This example provides for demonstrating how one can scroll forward and backward.

Figure 1: Scrolling forward and backward

Assume that the user scrolls backward three screens. This puts us at arrow 2 pointing to the current position with the following fetch:

EXEC SQL FETCH RELATIVE -12 SPJSC 
         INTO :SN, :PN, :JN, :QTY END-EXEC.

Four FETCH NEXT statements can be executed to fill the screen:

EXEC SQL FETCH NEXT SPJSC 
         INTO :SN, :PN, :JN, :QTY END-EXEC.

The user now scrolls backward two screens. This puts us at arrow 3 pointing to the current position with the following fetch.

EXEC SQL FETCH RELATIVE 8 SPJSC 
         INTO :SN, :PN, :JN, :QTY END-EXEC.

Four FETCH NEXT statements are executed to fill the screen.

Holes in a STATIC SCROLL Cursor

The number of rows in the temporary results table is fixed with a STATIC SCROLL cursor. However, holes are detected with a sensitive FETCH. There are delete and update holes. A delete hole results when a row in the base table is deleted. An update hole results when a selected row in the base table is changed. A SENSITIVE FETCH causes reevaluation of predicates against the base table. If a row no longer qualifies according to conditions in the WHERE clause, the row in the temporary results table is marked as an update hole and a +222 SQLCODE is returned. If the row is updated again to its original value according the WHERE clause, the update hole is unmarked in the temporary results table. The row in the temporary results table is visible and is returned with a fetch.

Updating with Scrollable Cursors

A sensitive scrollable cursor can be defined with the WITH FOR UPDATE OF clause and can be updated using WHERE CURRENT OF CURSOR. DB2 accomplishes this by locating the row in the base table and taking a lock. It verifies that the row qualifies according to the WHERE clause. It also verifies that selected columns have not changed.

An update is applied to the base and temporary results tables if the row in the base table qualifies. Changes can be seen by executing the program. If the row no longer qualifies according to the WHERE clause, the row is marked as a hole. The processing for the deletion of a row is similar except the row is never seen on a subsequent fetch.

Starting and Resulting Cursor Position

Kalpana Shyam of IBM Silicon Valley Lab provided the table in Figure 2 in her presentation “Scrollable Cursors: Fetching Opportunities for DB2 for OS/390" at the DB2 and Business Intelligence Technical Conference, October 16-20, 2000.

Figure 2: Starting and resulting cursor position

If a fetch encounters an update or delete hole, a +222 SQLCODE is returned to the program.

Performance Considerations

If too many rows qualify and must be placed in a temporary results table when the cursor is opened, you may wish to consider other alternatives for scrolling. It is important to limit the size of the temporary results table whenever possible. For example, apply as many predicates as possible and use FETCH FIRST n ROWS ONLY where n is the maximum number of rows through which the user can scroll. However, keep in mind that if rows must be written to a work file to satisfy an ORDER BY, for example, they will be written to a work file when using FETCH FIRST n ROWS ONLY.

The temporary results table is discarded when a screen is displayed (except with CICS conversational) and must be rebuilt when the cursor it opened after displaying a screen.

Scrollable cursors can include WITH HOLD and WITH RETURN. WITH HOLD can be used in batch programs to avoid closing the cursor and discarding the temporary results table. WITH RETURN results in blocks of data having to be retransmitted if sensitive data is required. 

INSENSITIVE cursors and fetches have a performance advantage because it is not necessary to access the base table to determine if the row has changed.