Accessing data by using a rowset-positioned cursor

A rowset-positioned cursor is a cursor that can return one or more rows for a single fetch operation. The cursor is positioned on the set of rows that are to be fetched.

Procedure

To access data by using a rowset-positioned cursor:

Execute a DECLARE CURSOR statement to define the result table on which the cursor operates. See Declaring a rowset cursor.
Execute an OPEN CURSOR to make the cursor available to the application. See Opening a rowset cursor.
Specify what the program is to do when all rows have been retrieved. See Specifying the action that the rowset cursor is to take when it reaches the end of the data.
Execute multiple SQL statements to retrieve data from the table or modify selected rows of the table. See Executing SQL statements by using a rowset cursor.
Execute a CLOSE CURSOR statement to make the cursor unavailable to the application. See Closing a rowset cursor.

Results

Your program can have several cursors, each of which performs the previous steps.

Declaring a rowset cursor
Before you can use a rowset-positioned cursor to retrieve rows, you must declare a cursor that is enabled to fetch rowsets. When you declare a cursor, you identify a set of rows that are to be accessed with the cursor.
Opening a rowset cursor
After you declare a rowset cursor, you need to tell DB2 that you are ready to process the first rowset of the result table. This action is called opening the cursor.
Specifying the action that the rowset cursor is to take when it reaches the end of the data
Your program must be coded to recognize and handle an end-of-data condition whenever you use a rowset cursor to fetch rows.
Executing SQL statements by using a rowset cursor
You can use rowset cursors to execute multiple-row FETCH statements, positioned UPDATE statements, and positioned DELETE statements.
Closing a rowset cursor
Close a rowset cursor when it finishes processing rows if you want to free the resources or if you want to use the cursor again. Otherwise, you can let DB2 automatically close the cursor when the current transaction terminates or when your program terminates.

Parent topic: Retrieving a set of rows by using a cursor