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 section looks at how an application program can use a cursor to retrieve rows from a table.

Row Retrieval

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. 

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, 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.

NOTE

Some result tablesfor example, the result of joining two or more tablescannot be updated.

OPEN Statement

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. 

NOTE

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

• Whether DB2 must perform any sorts before it can retrieve rows from the result table

• Whether DB2 uses parallelism to process the SELECT statement associated with the cursor

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 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.

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 global temporary table but can be used to modify the rows of a declared global 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 but can be used to delete rows from a declared global 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.

CLOSE Statement

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

• A CLOSE cursor or ROLLBACK statement is issued

• A Call Attach Facility (CAF) CLOSE function call or an RRSAF TERMINATE THREAD function call is issued

• The connection from a remote client application is released

• 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.

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.)

NOTE

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.

Table 10-5. Types of Cursors

Cursor Type	Result Table	Visibility of Own Cursor's Changes	Visibility of Other Cursors' Changes	Updatability
Nonscrollable (join, sort, and so on)	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	No declared temporary table, base table access	Yes	Yes	Yes

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.

NOTE

If it references more than one table or contains a GROUP BY or similar clause, the SELECT statement becomes read-only.

Nonscrollable Cursor

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 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:

• DECLARE C1 INSENSITIVE SCROLL/FETCH INSENSITIVE: read-only cursor; not aware of updates or deletes in base table

• DECLARE C1 SENSITIVE STATIC SCROLL/FETCH INSENSITIVE: updatable cursor; aware of own updates or deletes within cursor; other changes to base table not visible to cursor; all inserts not recognized

• DECLARE C1 SENSITIVE STATIC SCROLL/FETCH SENSITIVE: Updatable cursor; aware of own updates and deletes within cursor; sees all committed updates and deletes; all inserts not recognized

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) 

NOTE

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.