Types of cursors

You can declare row-positioned or rowset-positioned cursors in a number of ways. These cursors can be scrollable or not scrollable, held or not held, or returnable or not returnable.

In addition, you can declare a returnable cursor in a stored procedure by including the WITH RETURN clause; the cursor can return result sets to a caller of the stored procedure.

Scrollable and non-scrollable cursors:

When you declare a cursor, you tell DB2® whether you want the cursor to be scrollable or non-scrollable by including or omitting the SCROLL clause. This clause determines whether the cursor moves sequentially forward through the result table or can move randomly through the result table.

Using a non-scrollable cursor:

The simplest type of cursor is a non-scrollable cursor. A non-scrollable cursor can be either row-positioned or rowset-positioned. A row-positioned non-scrollable cursor moves forward through its result table one row at a time. Similarly, a rowset-positioned non-scrollable cursor moves forward through its result table one rowset at a time.

A non-scrollable cursor always moves sequentially forward in the result table. When the application opens the cursor, the cursor is positioned before the first row (or first rowset) in the result table. When the application executes the first FETCH, the cursor is positioned on the first row (or first rowset). When the application executes subsequent FETCH statements, the cursor moves one row ahead (or one rowset ahead) for each FETCH. After each FETCH statement, the cursor is positioned on the row (or rowset) that was fetched.

After the application executes a positioned UPDATE or positioned DELETE statement, the cursor stays at the current row (or rowset) of the result table. You cannot retrieve rows (or rowsets) backward or move to a specific position in a result table with a non-scrollable cursor.

Using a scrollable cursor:

To make a cursor scrollable, you declare it as scrollable. A scrollable cursor can be either row-positioned or rowset-positioned. To use a scrollable cursor, you execute FETCH statements that indicate where you want to position the cursor.

If you want to order the rows of the cursor's result set, and you also want the cursor to be updatable, you need to declare the cursor as scrollable, even if you use it only to retrieve rows (or rowsets) sequentially. You can use the ORDER BY clause in the declaration of an updatable cursor only if you declare the cursor as scrollable.

Declaring a scrollable cursor:

To indicate that a cursor is scrollable, you declare it with the SCROLL keyword. The following examples show a characteristic of scrollable cursors: the sensitivity.

The following figure shows a declaration for an insensitive scrollable cursor.

EXEC SQL DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
  SELECT DEPTNO, DEPTNAME, MGRNO
  FROM DSN8A10.DEPT
  ORDER BY DEPTNO
END-EXEC.

Declaring a scrollable cursor with the INSENSITIVE keyword has the following effects:

The size, the order of the rows, and the values for each row of the result table do not change after the application opens the cursor.
Rows that are inserted into the underlying table are not added to the result table.
The result table is read-only. Therefore, you cannot declare the cursor with the FOR UPDATE clause, and you cannot use the cursor for positioned update or delete operations.

The following figure shows a declaration for a sensitive static scrollable cursor.

EXEC SQL DECLARE C2 SENSITIVE STATIC SCROLL CURSOR FOR
  SELECT DEPTNO, DEPTNAME, MGRNO
  FROM DSN8A10.DEPT
  ORDER BY DEPTNO
END-EXEC.

Declaring a cursor as SENSITIVE STATIC has the following effects:

The size of the result table does not grow after the application opens the cursor.
Rows that are inserted into the underlying table are not added to the result table.
The order of the rows does not change after the application opens the cursor.
If the cursor declaration contains an ORDER BY clause, and the columns that are in the ORDER BY clause are updated after the cursor is opened, the order of the rows in the result table does not change.
When the application executes positioned UPDATE and DELETE statements with the cursor, those changes are visible in the result table.
When the current value of a row no longer satisfies the SELECT statement that was used in the cursor declaration, that row is no longer visible in the result table.
When a row of the result table is deleted from the underlying table, that row is no longer visible in the result table.
Changes that are made to the underlying table by other cursors or other application processes can be visible in the result table, depending on whether the FETCH statements that you use with the cursor are FETCH INSENSITIVE or FETCH SENSITIVE statements.

The following figure shows a declaration for a sensitive dynamic scrollable cursor.

EXEC SQL DECLARE C2 SENSITIVE DYNAMIC SCROLL CURSOR FOR
  SELECT DEPTNO, DEPTNAME, MGRNO
  FROM DSN8A10.DEPT
  ORDER BY DEPTNO
END-EXEC.

Declaring a cursor as SENSITIVE DYNAMIC has the following effects:

The size and contents of the result table can change with every fetch.
The base table can change while the cursor is scrolling on it. If another application process changes the data, the cursor sees the newly changed data when it is committed. If the application process of the cursor changes the data, the cursor sees the newly changed data immediately.
The order of the rows can change after the application opens the cursor.
If the SELECT statement of the cursor declaration contains an ORDER BY clause, and columns that are in the ORDER BY clause are updated after the cursor is opened, the order of the rows in the result table changes.
When the application executes positioned UPDATE and DELETE statements with the cursor, those changes are visible. In addition, when the application executes insert, update, or delete operations (within the application but outside the cursor), those changes are visible.
All committed inserts, updates, and deletes by other application processes are visible.
Because the FETCH statement executes against the base table, the cursor needs no temporary result table. When you define a cursor as SENSITIVE DYNAMIC, you cannot specify the INSENSITIVE keyword in a FETCH statement for that cursor.

Visibility of changes to a result table:

Whether a cursor can view its own changes or the changes that are made to the data by other processes or cursors depends on how the cursor is declared, and the updatability of the cursor. Visibility also depends on the type of fetch operation that is executed with the cursor. The following table summarizes the visibility of changes to a result table for each type of cursor.

Declared cursor type	Cursor is updatable or read-only?	Changes by the cursor are visible in the result table?³	Changes by other cursors or processes are visible to the result table?
NO SCROLL (result table is materialized)	Read-only¹	Not applicable	No
NO SCROLL (result table is not materialized)	Updatable²	Yes	Yes
INSENSITIVE SCROLL	Read-only⁴	Not applicable	No
SENSITIVE STATIC SCROLL	Updatable^2,6	Yes	Depends on the explicitly or implicitly specified sensitivity in the FETCH clause⁵
SENSITIVE DYNAMIC SCROLL	Updatable²	Yes	Yes⁷
Notes: The content of the SELECT statement of the cursor makes the cursor implicitly read-only. The cursor is updatable only if FOR READ ONLY or FOR FETCH ONLY is not specified as part of the SELECT statement of the cursor, and there is nothing in the content of the SELECT statement makes the cursor implicitly read-only. If INSENSITIVE is specified on FETCH, only changes made by the same cursor are visible, assuming that the rows being fetched have not already been read by a SENSITIVE FETCH on the same cursor. An INSENSITIVE cursor is read-only if an updatability clause is not specified. The sensitivity clause in a FETCH statement affects the visibility of others' changes as follows: For FETCH INSENSITIVE: Only positioned updates and deletes that are made by the same cursor are visible. For FETCH SENSITIVE: All updates and deletes are visible. Positioned updates and deletes are disallowed if the values of the selected columns do not match the current values of the columns in the base table, even if the row satisfies the predicate of the SELECT statement of the cursor. All updates and deletes that are made by this cursor, and committed changes that are made by other processes are visible on subsequent FETCH statements. Inserts that are made by this process are also be visible as the result table is scrolled. Inserts by other processes into the base tables underlying the result table are visible after they are committed.