Using Cursors

The DB2/SQL Service supports the SQL statements associated with cursors -- a mechanism for processing the results of queries one row at a time.

When to Use a Cursor Programming With Cursors
A Cursor Example Declaring The Cursor
Opening The Cursor Fetching Rows
Closing The Cursor Freeing The Cursor
Updating And Deleting Rows

When to Use a Cursor

The REXXTOOLS multi-row SELECT in many cases is a better choice for processing queries. The multi-row SELECT produces all of the REXX variables for the result table with one command.

Despite the convenience of the the multi-row SELECT, there are numerous occasions where the use of a cursor is warranted, and even necessary:

If the number of rows in the result table is likely to be large, you should use a cursor. The multi-row SELECT requires the entire result table to fit into the program's region. For very large queries, this simply is not feasible. By using a cursor, you can control the amount of result table information retained in the region at any one time.
If you want to update and delete rows based upon criteria that cannot be specified in "searched" UPDATE or DELETE commands, you will need to use a cursor.
In situations where you need to control when the cursor is closed, a cursor is required.

Programming With Cursors

Using a cursor to process a query is similar to using a ddname to read a file. The steps are as follows:

Associate a query (a SELECT) with a cursor name. The DECLARE CURSOR command is used for this purpose.
Open the cursor. At this point, the interface and DB2 process the query and produce a result table. You may think of the result table as a file whose records contain the fields listed on the SELECT. The result table may contain zero or an unlimited number of rows.
Use the FETCH command to retrieve a row into program variables.
Perform whatever processing is required on the row. You may for example, delete the row (from the source table(s) - not just the result table), or update fields in the row (again, in the source table(s)).
Return to step 3 to retrieve the next row. This process is repeated until all "interesting" rows have been processed or until a SQLCODE of 100 (end-of-file) is returned.
Close the cursor. This terminates processing of the result table.
Free the cursor. This frees internal data structures associated with the cursor.

This process is often referred to as a "FETCH loop".

A Cursor Example

The following program demonstrates how cursors are used:

/* REXX */
address sql
 
/* Declare variables */
"exec sql begin declare section;"
"exec sql declare ln variable varchar(15);"
"exec sql declare sal variable decimal(9,2);"
"exec sql end declare section;"
 
/* Associate a SELECT statement with a cursor name */
"exec sql declare empcsr cursor for",
  "select lastname, salary",
    "from dsn8230.emp",
    "where lastname like 'H%';"
 
/* Open the cursor - DB2 creates a result table */
"exec sql open empcsr;"
if rc<>0 then exit
 
/* Loop through the rows of the result table */
"exec sql fetch empcsr into :ln, :sal;"
do while rc=0
  say ln sal
  "exec sql fetch empcsr into :ln, :sal;"
end
 
/* Close the cursor - DB2 throws away the result table */
"exec sql close empcsr;"
 
/* Free the cursor's data structure */
"exec sql free empcsr cursor;"

The next sections discuss each of these steps in detail.

Declaring The Cursor

The DECLARE CURSOR statement is used to associate a cursor name with a query. The syntax of the DECLARE CURSOR command is as follows:

DECLARE cursor-name CURSOR [WITH HOLD]
  FOR select-statement

Where cursor-name is a short identifier (8 or fewer characters), and select-statement is a valid DB2 select-statement (refer to the DB2 SQL Reference). The DECLARE CURSOR command causes the SQL host command environment to reserve an internal, statically declared cursor for use with the cursor-name. If you specify "WITH HOLD", the cursor will remain open across COMMITs. If you do not specify "WITH HOLD", the cursor will be closed automatically by a COMMIT statement. Both "hold" and "no hold" cursors are closed automatically by a ROLLBACK statement.

Note: Each task may have up to 10 "no hold" and 10 "hold" cursors in use at any one time.

The select-statement component, which is saved for use in processing the OPEN statement, may contain host variable expressions. The values of host variable expressions are resolved when the cursor is opened (not when it is declared). An INTO clause is not supported in this context. You may, however, use an INTO clause in a FETCH statement.

The data structures associated with a declared cursor are valid until a FREE CURSOR statement is executed, or the task under which the cursor was declared terminates.

Opening The Cursor

When an OPEN is executed, the interface PREPAREs the SELECT statement associated with the cursor, and executes an internal static OPEN statement. The OPEN causes DB2 to process the query, thereby creating a result table (a temporary table used to hold the retrieved columns and rows).

The OPEN statement has the following syntax:

OPEN cursor-name

Where cursor-name is the name of a cursor that was previously DECLAREd.

The OPEN statement does not support a USING clause, because you are permitted to code host variable references (see the topic "Variables") directly in the SELECT component of the DECLARE CURSOR command. When an OPEN statement is processed, all host variable expressions contained within the select-statement are resolved.

Fetching Rows

Rows are read from the result table using the FETCH statement. The FETCH statement has the following syntax:

FETCH cursor-name
  [INTO host-var-expres[,host- var-expres...]]

Where cursor-name is the name of a cursor that was previously DECLAREd and OPENed. The FETCH statement returns the next row in the result table (or a non- zero SQLCODE if no more rows are available). The column values of the fetched row are converted as required, and placed into REXX variables. The names of these variables can be specified in the optional INTO clause. If you do not specify an INTO clause, the names of the columns retrieved are used to create REXX variable names (see "Variables" for more on this subject).

Closing The Cursor

To close a cursor you use the CLOSE statement. Closing a cursor frees its result table. The CLOSE statement has the following syntax:

CLOSE {cursor-name|*}

Where cursor-name is the name of a cursor that was previously DECLAREd and OPENed. If you do not explicitly close a cursor, DB2 will close it when a COMMIT is issued ("no hold" cursors only), or when the task that executed the OPEN statement terminates. If you use the asterisk (*) format, all currently open cursors will be closed.

Note that closing a cursor does not free the data structures associated with the cursor-name. To do that you must use the FREE statement.

Freeing The Cursor

After you have closed a cursor, and if you don't plan on opening it again, you should free the cursor so that the internal static cursor can be reused. To free a cursor, use the FREE statement (Refer to the topic "FREE CURSOR").

Updating And Deleting Rows

The UPDATE and DELETE statements may be used to process the current row of the result table. To update or delete the current row of the result table, use the "WHERE CURRENT OF cursor-name" clause with the appropriate statement. For example:

/* Declare the cursor */
"declare empcsr cursor for",
  "select lastname, salary",
    "from dsn8230.emp",
  "for update of salary"
 
/* open the cursor */
"open empcsr"
if rc<>0 then exit
 
/* Loop through the rows of the result table
   updating rows where the salary is zero */
basesal = 10000
"fetch empcsr into :lname, :salary"
do while rc=0
  say lname salary
  if salary = 0 then
    "update dsn8230.emp",
      "set salary = :basesal",
      "where current of empcsr"
  "fetch empcsr into :lname, :salary"
end
 
/* close and free the cursor */
"close empcsr"
"free empcsr cursor"

Notes:

Only non-read-only result tables can be updated or deleted. Refer to the DB2 SQL Reference for the definition of read-only tables.
UPDATE and DELETE can also be used to update many rows at once. For example, all of the SQL statements in the example above could be replaced with the following UPDATE statement:
```
basesal = 10000
"update dsn8410.emp",
   "set salary = :basesal",
 "where salary = 0"
```