DB2 10 - Application programming and SQL - Retrieving a set of rows by using a cursor

Cursors
A cursor is a mechanism that points to one or more rows in a set of rows. The rows are retrieved from a table or in a result set that is returned by a stored procedure. Your application program can use a cursor to retrieve rows from a table.

Accessing data by using a row-positioned cursor
A row-positioned cursor is a cursor that points to a single row and retrieves at most a single row at a time from the result table. You can specify a fetch request to specify which rows to retrieve, relative to the current cursor position.

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.

Retrieving rows by using a scrollable cursor
A scrollable cursor is cursor that can be moved in both a forward and a backward direction. Scrollable cursors can be either row-positioned or rowset-positioned.

Accessing XML or LOB data quickly by using FETCH WITH CONTINUE
Use the FETCH WITH CONTINUE statement to improve the performance of some queries that reference XML and LOB columns with unknown or very large maximum lengths.

Determining the attributes of a cursor by using the SQLCA
An SQL communications area (SQLCA) is an area that is set apart for communication with DB2 and consists of a collection of variables. Using the SQLCA is one way to get information about any open cursors. Alternatively, you can use the GET DIAGNOSTICS statement.

Determining the attributes of a cursor by using the GET DIAGNOSTICS statement
Using the GET DIAGNOSTICS statement is one way to get information about any open cursors. Alternatively, you can use the SQLCA.

Scrolling through previously retrieved data
To scroll backward through data, use a scrollable cursor, or use a ROWID column or identity column to retrieve data in reverse order.

Updating previously retrieved data
To scroll backward through data and update it, use a scrollable cursor that is declared with the FOR UPDATE clause.

FETCH statement interaction between row and rowset positioning
When you declare a cursor with the WITH ROWSET POSITIONING clause, you can intermix row-positioned FETCH statements with rowset-positioned FETCH statements.

Examples of fetching rows by using cursors
You can use SQL statements that you include in a COBOL program to define and use non-scrollable cursor for row-positioned updates, scrollable cursors to retrieve rows backward, non-scrollable cursors for rowset-positioned updates, and scrollable cursors for rowset-positioned operations.

Retrieving a set of rows by using a cursor

About this task