Using Cursors 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 sections 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. In an application program, there needs to be a way 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, open at the same time. DECLARE Cursor To define and identify a set of rows to be accessed with a cursor, issue a DECLARE CURSOR statement. The DECLARE CURSOR 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. Updating a Column If any of the columns are to be updated in the rows of the identified table, include the FOR UPDATE clause. The FOR UPDATE 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. NOTE  | 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, there is no 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, the UPDATE...WHERE CURRENT OF statement can be used to update the row. NOTE | | Some result tablesfor example, the result of joining two or more tablescannot be updated. |
OPENing the Cursor To tell DB2 you are ready to process the first row of the result table or result set, an OPEN statement must be issued. DB2 then uses the SELECT statement within the 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. NOTE | | There is no way in DB2 to determine the number of rows that qualified during the open. An empty result table or result set is identified by a 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. There is more information on special registers 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 Executing SQL statements Once a cursor is opened, one of the following SQL statements is executed using the cursor: -
FETCH -
positioned UPDATE -
positioned DELETE FETCH statements Execute a FETCH statement for one of the following purposes: -
To copy data from a row of the result table into one or more host variables. -
To position the cursor before a positioned UPDATE or positioned DELETE operation is performed. 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 current row contents 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. NOTE | | BLOCK FETCH cannot be used when a positioned UPDATE or DELETE is performed. |
Positioned UPDATE 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: -
The row cannot be updated if the update violates any unique, check, or referential constraints. -
An UPDATE statement cannot be used to modify the rows of a created temporary table. However, it can be used to modify the rows of a declared temporary table (covered later in this chapter). -
If the right side of the SET clause in the UPDATE statement contains a subselect, that subselect cannot include a correlated name for a table that is being updated. Positioned DELETE 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: -
A DELETE statement cannot be used with a cursor to delete rows from a created global temporary table. However, it can be used to delete rows from a declared temporary table. -
After a row has been deleted, you cannot update or delete another row using that cursor until you execute a FETCH statement to position the cursor on another row. -
A row cannot be deleted if the delete violates any referential constraints. CLOSEing the Cursor 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 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 closes after a COMMIT operation. When a cursor is declared, the inclusion or exclusion of the WITH HOLD clause tells DB2 whether or not 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 -
A CLOSE cursor, ROLLBACK, or CONNECT statement is issued. -
A Call Attach Facility (CAF) CLOSE function call or an RRSAF TERMINATE THREAD function call is issued. -
The application program terminates. 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: -
Do not use DECLARE CURSOR WITH HOLD with the new user sign-on from a DB2 attachment facility, because all open cursors are closed. -
Do not declare a WITH HOLD cursor in a thread that could become inactive. If you do, its locks are held indefinitely. There are also some restrictions when using CURSOR WITH HOLD with IMS or CICS programs. (Refer to IBM DB2 Application Programming Guide and SQL Guide for more details.) NOTE | | The use of CURSOR WITH HOLD also will prevent some options in DB2 that are used for performance (e.g., parallelism, drains needed for online REORG). |
A simple example of declaring a held cursor is show below. 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 nonscrollable. They can also be held or not held. The following sections discuss these characteristics in more detail. Scrollable and Nonscrollable 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. See Table 5-2. Table 5-2. Types of Cursors | Cursor Type | Result Table | Visibility of Own Cursor's Changes | Visibility of Other Cursors' Changes | Updateability [a] | | Nonscrollable (join, sort , etc.) | Fixed, work file | No | No | No | | Nonscrollable | No work file, base table access | Yes | Yes | Yes | | Insensitive Scroll | Fixed, declared temporary table | No | No | No | | Sensitive Static Scroll | Fixed, declared temporary table | Yes ( inserts not allowed) | Yes (not inserts) | Yes | | Sensitive Dynamic Scroll (not supported yet) | No declared temporary table, base table access | Yes | Yes | Yes | [a] Updateability is affected by read-only properties. NOTE | | If the SELECT statement references more than one table or contains a GROUP BY or similar clause, then it becomes read-only! |
Using a Nonscrollable Cursor The simplest type of cursor is a nonscrollable cursor. A nonscrollable cursor 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. Using a Scrollable 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 some means other than the cursor are visible to the result table. Sensitivity can be INSENSITIVE or SENSITIVE. INSENSITIVE means that the only time that 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 tables change. Table 5-3 is a summary of the sensitivity options. Table 5-3. Cursor Sensitivity Options | DECLARE C1 INSENSITIVE SCROLL FETCH INSENSITIVE | DECLARE C1 SENSITIVE STATIC SCROLL FETCH INSENSITIVE | DECLARE C1 SENSITIVE STATIC SCROLL FETCH SENSITIVE | | | -
Updateable cursor -
Aware of own updates or deletes within cursor -
Other changes to base table not visible to cursor -
All inserts not recognized | -
Updateable cursor -
Aware of own updates and deletes within cursor -
Sees all committed updates and deletes -
All inserts not recognized | Scrollable Cursor Fetching There are several options 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 different 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) NOTE | | In a distributed environment, you can use scrollable cursors only if you use DRDA access. |
| 
