IBM Mainframe Forum

Any insert after the OPEN will have no effect on fetching.

To retrieve rows from the result table of a cursor, you must execute a FETCH statement when the cursor is open. The only way to change the state of a cursor from closed to open is to execute an OPEN statement.

Effect of a temporary copy of a result table: DB2 can process a cursor in two different ways:

* It can create a temporary copy of the result table during the execution of the OPEN statement. You can specify INSENSITIVE SCROLL on the cursor to force the use of a a temporary copy of the result table.

* It can derive the result table rows as they are needed during the execution of later FETCH statements.

If the result table is not read-only, DB2 uses the latter method. If the result table is read-only, either method could be used. The results produced by these two methods could differ in the following respects:


| When a temporary copy of the result table is used: An error can occur that
| would otherwise not occur until some later FETCH statement. INSERT
| statements that are executed while the cursor is open cannot affect the
| result table once all the rows have been materialized in the temporary
| copy of the result table. For a scrollable insensitive cursor, UPDATE and
| DELETE statements that are executed while the cursor is open cannot affect
| the result table. For a scrollable sensitive static cursor, UPDATE and
| DELETE statements can affect the result table if the rows are subsequently
| fetched with sensitive FETCH statements.

When a temporary copy of the result table is not used: INSERT, UPDATE, and DELETE statements that are executed while the cursor is open can affect the result table if they are issued from the same application process. The effect of such operations is not always predictable.

For example, if cursor C is positioned on a row of its result table defined as SELECT * FROM T, and you insert a row into T, the effect of that insert on the result table is not predictable because its rows are not ordered. A later FETCH C might or might not retrieve the new row of T. To avoid these changes, you can specify INSENSITIVE SCROLL for the cursor to force the use of a temporary copy of the result table.

Touche.

However this only applies for a scrollable cursor

When a temporary copy of the result table is not used: INSERT, UPDATE, and DELETE statements that are executed while the cursor is open can affect the result table if they are issued from the same application process. The effect of such operations is not always predictable.