![]() | |
|
Cursors bound with cursor stability that are used in block fetch operations are particularly vulnerable to reading data that has already changed. In a block fetch, database access prefetches rows ahead of the row retrieval controlled by the application. During that time the cursor might close, and the locks might be released, before the application receives the data. Thus, it is possible for the application to fetch a row of values that no longer exists, or to miss a recently inserted row. In many cases, that is acceptable; a case for which it is not acceptable is said to require data currency.
If your application requires data currency for a cursor, you need to prevent block fetching for the data to which it points. To prevent block fetching for a distributed cursor, declare the cursor with the FOR UPDATE clause.
You can declare cursors, both row-positioned and rowset-positioned, as scrollable or not scrollable, held or not held, and 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 DSN8910.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.
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 DSN8910.DEPT ORDER BY DEPTNO END-EXEC.
Declaring a cursor as SENSITIVE STATIC has the following effects:
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 DSN8910.DEPT ORDER BY DEPTNO END-EXEC.
Declaring a cursor as SENSITIVE DYNAMIC has the following effects:
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.
If you specify an ORDER BY clause for a SENSITIVE DYNAMIC cursor, DB2 might choose an index access path if the ORDER BY is fully satisfied by an existing index. However, a dynamic scrollable cursor that is declared with an ORDER BY clause is not updatable.
Static scrollable cursor
Both the INSENSITIVE cursor and the SENSITIVE STATIC cursor follow the static cursor model:
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.
Dynamic scrollable cursor
When you declare a cursor as SENSITIVE, you can declare it either STATIC or DYNAMIC. The SENSITIVE DYNAMIC cursor follows the dynamic cursor model:
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 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.
Held and non-held cursors
A held cursor DOES NOT close after a commit operation. A cursor that is not held closes after a commit operation. You specify whether you want a cursor to be held or not held by including or omitting the WITH HOLD clause when you declare the cursor.
After a commit operation, the position of a held cursor depends on its type:
A non-scrollable cursor that is held is positioned after the last retrieved row and before the next logical row. The next row can be returned from the result table with a FETCH NEXT statement.
A static scrollable cursor that is held is positioned on the last retrieved row. The last retrieved row can be returned from the result table with a FETCH CURRENT statement.
A dynamic scrollable cursor that is held is positioned after the last retrieved row and before the next logical row. The next row can be returned from the result table with a FETCH NEXT statement. DB2 returns SQLCODE +231 for a FETCH CURRENT statement.
A held cursor can close when:
You issue a CLOSE cursor, ROLLBACK, or CONNECT statement
You issue a CAF CLOSE function call or an RRSAF TERMINATE THREAD function call
The application program terminates.
If the program abnormally terminates, the cursor position is lost. To prepare for restart, your program must reposition the cursor.
![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |