Use a cursor in an application program to retrieve multiple rows from a table or from a result set returned by a stored procedure. This section looks at how an application program can use a cursor to retrieve rows from a table.
When the SELECT statement associated with the cursor is executed, a set of rows is retrieved. The set of rows returned is referred to as the result table, or result set. An application program must be able to retrieve one row at a time from the result set into host variables. A cursor performs that function. A program can have several cursors, all open at the same time. A cursor can also be used to retrieve multiple rows at a time. This is covered in Chapter 12.
DECLARE CURSOR Statement
To define and identify a set of rows to be accessed with a cursor, issue a DECLARE CURSOR statement. This statement names a cursor and specifies a SELECT statement. The SELECT statement defines the criteria for the rows that will make up the result table. The simplest form of the DECLARE CURSOR statement is as follows:
EXEC SQL DECLARE CERTCUR CURSOR FOR SELECT TCID, NAME, CODE FROM DB2USER1.TEST_CENTER WHERE TCID = :TCIDHV END-EXEC.
If any of the columns are to be updated in the rows of the identified table, include the FOR UPDATE clause. This clause has two forms: one form for when the columns to be updated are known and another for when the columns are not known. Use the following form when the columns intended for update are known:
EXEC SQL DECLARE CERTCUR CURSOR FOR SELECT TCID, NAME, CODE FROM DB2USER1.TEST_CENTER WHERE TCID = :TCIDHV FOR UPDATE OF CODE END-EXEC.
If a cursor might be used to update any of the columns of the table, use the following form of the FOR UPDATE clause, which allows for updates to any columns of the table that can be updated:
EXEC SQL DECLARE CERTCUR CURSOR FOR SELECT TCID, NAME, CODE FROM DB2USER1.TEST_CENTER WHERE TCID = :TCIDHV FOR UPDATE END-EXEC.
DB2 must do more processing when you use the FOR UPDATE clause without a column list than when you use the FOR UPDATE OF clause with a column list. Therefore, if only a few columns of a table are going to be updated, the program can run more efficiently if a column list is included.
A column of the identified table can be updated even though it is not part of the result table. In this case, you do not need to name the column in the SELECT statement. When the cursor retrieves a row, using FETCH, that contains a column value you want to update, you can use the UPDATE...WHERE CURRENT OF statement to update the row.
Some result tablesfor example, the result of joining two or more tablescannot be updated.
To tell DB2 that you are ready to process the first row of the result table or result set, you must issue an OPEN statement. DB2 then uses the SELECT statement within DECLARE CURSOR to identify a set of rows. If host variables are used in that SELECT statement, DB2 uses the current value of the variables to select the rows. The result table that satisfies the search conditions can contain zero, one, or many rows. The OPEN statement looks like this:
EXEC SQL OPEN CERTCUR END-EXEC.
DB2 cannot determine the number of rows that qualified during the OPEN. An empty result table or result set is identified by an SQLCODE of +100 on the subsequent FETCH, UPDATE, or DELETE statement.
When used with cursors, DB2 evaluates CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP special registers once when the OPEN statement executes. (More information on special registers is provided later in this chapter.) DB2 uses the values returned in the registers on all subsequent FETCH statements.
Two factors that influence the amount of time that DB2 requires to process the OPEN statement are
Once a cursor is opened, one of the following SQL statements is executed, using the cursor:
Execute a FETCH statement for one of the following purposes:
The simplest form of the FETCH statement looks like this:
EXEC SQL FETCH CERTCUR INTO :TCIDHV,:NAMEHV, ... END-EXEC.
The SELECT statement within the DECLARE CURSOR statement identifies the result table from which to fetch rows, but DB2 does not retrieve any data until the application program executes a FETCH statement.
When the program executes the FETCH statement, DB2 uses the cursor to point to a row in the result table. That row is called the current row. DB2 then copies the contents of the current row into the program host variables that you specified in the INTO clause of FETCH. This sequence repeats for each subsequent FETCH until you have processed all the rows in the result table.
The row that DB2 points to when a FETCH statement is executed depends on whether the cursor is declared as scrollable or nonscrollable (covered later in this chapter). When querying a remote subsystem with FETCH, consider using BLOCK FETCH for better performance. BLOCK FETCH processes rows ahead of the current row.
BLOCK FETCH cannot be used when a positioned UPDATE or DELETE is performed.
After the program has executed a FETCH statement to retrieve the current row, a positioned UPDATE statement can be used to modify the data in that row. An example of positioned UPDATE is as follows:
EXEC SQL UPDATE DB2USER1.TEST_CENTER SET CODE = 123456 WHERE CURRENT OF CERTCUR END-EXEC.
A positioned UPDATE statement updates the row that cursor name (CERTCUR) points to. A positioned UPDATE statement must meet these conditions.
After the program has executed a FETCH statement to retrieve the current row, a positioned DELETE statement can be used to delete that row. An example of a positioned DELETE statement follows:
EXEC SQL DELETE FROM DB2USER1.TEST_CENTER WHERE CURRENT OF CERTCUR END-EXEC.
A positioned DELETE statement deletes the row that CERTCUR points to. A positioned DELETE statement must meet these conditions.
When the program is finished processing the rows of the result table, issue a CLOSE statement to close the cursor:
EXEC SQL CLOSE CERTCUR END-EXEC.
This statement will destroy the result set that was created during the OPEN cursor.
Cursor WITH HOLD
A held cursor, which is declared WITH HOLD, does not close after a COMMIT operation. A cursor that is not declared WITH HOLD is automatically closed after a COMMIT operation. When a cursor is declared, the inclusion or exclusion of the WITH HOLD clause tells DB2 whether the cursor is to be held. After a COMMIT operation, a held cursor is positioned after the last row retrieved and before the next logical row of the result table to be returned.
A held cursor will close when
If the program abnormally terminates, the cursor position is lost. To prepare for restart, your program must reposition the cursor. The following restrictions apply to cursors that are declared WITH HOLD.
Some restrictions apply when using CURSOR WITH HOLD with IMS or CICS programs. (Refer to IBM DB2 Application Programming Guide and SQL Guide for more details.)
The use of CURSOR WITH HOLD also will prevent some options in DB2 that are used for performance, such as parallelism and drains needed for an online REORG.
A simple example of declaring a held cursor follows:
EXEC SQL DECLARE CERTCUR CURSOR WITH HOLD FOR SELECT TCID, NAME, CODE FROM DB2USER1.TEST_CENTER WHERE TCID = :TCIDHV END-EXEC.
Types of Cursors
Cursors can be scrollable or not scrollable, as well as held or not held. Table 10-5 characterizes the types of cursors.
When you declare a cursor, you tell DB2 whether you want the cursor to be scrollable or nonscrollable, by including or omitting the SCROLL clause. This clause determines whether the cursor moves sequentially forward through the data set or has random access to the data set.
If it references more than one table or contains a GROUP BY or similar clause, the SELECT statement becomes read-only.
The simplest type of cursor is a nonscrollable cursor, which always moves sequentially forward in the result table. When a cursor is opened, it is positioned before the first row in the result table. When the first FETCH is executed, the cursor is positioned on the first row. The next FETCH statement moves the cursor one row ahead for each FETCH.
After each FETCH statement, the cursor is positioned on the row that was fetched. After an execution of a positioned UPDATE or positioned DELETE operation, the cursor stays at the current row of the result table. Rows cannot be retrieved backward or moved to a specific position in a result table with a nonscrollable cursor.
Scrollable cursors allow for movement to any row in the result table. DB2 uses declared temporary tables for processing scrollable cursors. The declaration for a scrollable cursor has the following form:
EXEC SQL DECLARE CERTCUR sensitivity STATIC SCROLL CURSOR FOR SELECT TCID, NAME, CODE FROM DB2USER1.TEST_CENTER WHERE TCID = :TCIDHV END-EXEC.
STATIC SCROLL indicates that the cursor is scrollable and follows the static model.
The sensitivity in the DECLARE statement indicates whether changes that are made to the underlying table after the cursor is opened are visible to the result table. Sensitivity can be INSENSITIVE or SENSITIVE. INSENSITIVE means that no changes to the underlying table after the cursor is opened are visible to the result table. SENSITIVE means that some or all changes that are made to the underlying table after the cursor is opened are visible to the result table.
The sensitivity clause of the FETCH statement determines which changes to the underlying table are visible to the result table. When the cursor is opened, the cursor is positioned before the first row. A clause is included in each FETCH statement to tell DB2 where to position the cursor in the result table. The FETCH clause for a scrollable cursor has the following form:
EXEC SQL FETCH sensitivity cursor-position cursor-name INTO :host-variable1, :host-variable2 ... END-EXEC.
Sensitivity in the FETCH statement indicates whether changes that are made to the underlying table by means other than the cursor are visible to the result table. Sensitivity can be INSENSITIVE or SENSITIVE. INSENSITIVE means that the only time changes to the underlying table are visible to the result table is when a positioned UPDATE or positioned DELETE using CERTCUR makes those changes. SENSITIVE means that the result table is updated when the underlying table changes. The sensitivity options can be summarized as follows:
Scrollable Cursor Fetching
Several options are available when fetching with a scrollable cursor. Cursor positioning becomes very flexible, as you can fetch forward and backward and do relative and absolute positioning. The following example shows several fetching options:
FETCH FETCH LAST (or FIRST) FETCH ABSOLUTE +7 FETCH INSENSITIVE RELATIVE -3 FETCH CURRENT (or BEFORE or AFTER) FETCH RELATIVE 2 FETCH PRIOR (or NEXT)
In a distributed environment, you can use scrollable cursors only if you use DRDA access.
Dynamic Scrollable Cursors
As of DB2 version 8, dynamic scrollable cursors can be implemented in applications, with better performance and more usability than static scrollable cursors. Dynamic scrollable cursors allow for base table access instead of having to use a declared global temporary table and do not materialize at any time. Each FETCH shows the most current activity and is sensitive to all INSERTs, UPDATEs, and DELETEs: no more delete holes and missing updates.
These cursors default to single-row fetch but also support multirow fetch, as well as positioned updates/deletes with multirow fetch. Order is always maintained with these cursors.
Following is an example of the syntax for defining a dynamic scrollable cursor:
DECLARE CERTCUR SENSITIVE DYNAMIC CURSOR FOR SELECT TCID, NAME, FROM TEST_CENTER
Some SQL statements require that result sets be placed in work files for joins. These statements are considered read-only and cannot support dynamic scrolling. A new option, ASENSITIVE, allows DB2 to decide whether the cursor is SENSITIVE DYNAMIC or INSENSITIVE based on the complexity of the SQL statement. If ASENSITIVE and read-only, the statement will be INSENSITIVE. If ASENSITIVE and not read-only, the statement will be SENSITIVE DYNAMIC.
The dynamic scrollable cursors will take advantage of backward index scans and backward sequential detection. They will support both index scan access and table scan access paths.