DB2 Version 9.7 for Linux, UNIX, and Windows

Retrieval of result sets from SQL data change statements using cursors

You can declare cursors for queries that retrieve result sets from SQL data-change operations. For example:

DECLARE C1 CURSOR FOR SELECT salary FROM FINAL TABLE
        (INSERT INTO employee (name, salary, level)
            SELECT name, income, band FROM old_employee)

Errors that occur when fetching from a cursor whose definition contains an SQL data-change operation will not cause a rollback of the modified rows. Even if the errors result in the cursor's closing, the row modifications will remain intact because they were completed when the application opened the cursor.

Upon the opening of such a cursor, the database manager completely executes the SQL data-change operation and the result set is stored in a temporary table. If an error occurs while the cursor opens, the changes made by the SQL data-change operation are rolled back. Further updates to the target table or view will not appear in the result table rows for cursors that retrieve result sets from SQL data-change operations. For example, an application declares a cursor, opens the cursor, performs a fetch, updates the table, and fetches additional rows. The fetches after the UPDATE statement will return those values that were determined during open cursor processing prior to the UPDATE statement.

You can declare scrollable cursors for queries that retrieve result sets from SQL data-change operations. The data modifications have already been written to the target table or view, because the result table is generated when you OPEN the cursor. Cursors with queries that select rows from an SQL data change operation must be defined as INSENSITIVE or ASENSITIVE.

Note: Scrollable cursors are supported only in CLI, JDBC, and SQLJ applications.

If you declare a cursor with the WITH HOLD option and the application performs a COMMIT, all of the data changes are committed. Cursors that you do not declare as WITH HOLD behave in the same manner. For all cursors, the SQL data-change operation included in the query is completely evaluated before any row is fetched.

When performing an explicit rollback for an OPEN CURSOR statement, or when rolling back to a save point prior to an OPEN CURSOR statement, all of the data changes for that cursor will be undone. For cursors with queries that retrieve result sets from SQL data-change operations, all data changes are undone after a rollback, but the cursor is retained and the previously inserted rows can still be fetched.