DB2 Cursor builds a results table to hold all the rows retrieved by executing a SELECT statement. DB2 uses a cursor to make the rows, from the results table, available to the application program. A cursor identifies the current row of the results table. When you use a cursor, the program can retrieve each row sequentially from the results table until end-of-data (i.e the not found condition SQLCODE=100). The SELECT statement used must be within a DECLARE CURSOR statement and cannot include an INTO clause. The DECLARE CURSOR statement defines and names the cursor, identifying the set of rows to be retrieved with the SELECT statement of the cursor.
The results table is processed very much like a sequential data set. The cursor must be opened (with an OPEN statement) before any rows are retrieved. A FETCH statement is used to retrieve the cursor’s current row. FETCH can be executed repeatedly until all rows have been retrieved. When the end-of-data condition occurs the cursor must be closed with a CLOSE statement. You can perform below mentioned distinct operations on cursors -
DECLARE DB2 cursor
Defines the cursor, gives it a name unique to the program in which it is embedded, and assigns an SQL statement to the cursor name. The DECLARE statement does not execute the SQL statement; it merely defines the SQL statement. Declarations for host variables that are referenced in a DECLARE CURSOR statement must precede the DECLARE CURSOR statement. The DECLARE CURSOR statement must precede any statement that references the cursor.
EXEC SQL DECLARE cursor-name CURSOR FOR SELECT column1, column2,… FROM table-name WHERE condition FOR UPDATE OF column,… FOR FETCH ONLY END-EXEC.
Cursors in the application program are used to retrieve the multiple rows and process them one by one. Generally by using a singleton select statement in the application program, it is possible to retrieve only one row at a time as the host variable structure allows keeping one value at a time.
EXAMPLE
Select col1,col2 from tab1
Where col1 =’string1’
Into : ww-host1
: ww-host2
The DECLARE CURSOR statement is usually placed in Working-Storage but it can go in the Procedure Division.
FOR UPDATE OF Clause
This clause must be specified if you intend to update any (or all) of the rows in the identified table. In this clause you name each column you intend to update. If you do not specify the names of columns you will later update, you will receive an error code in the SQLCODE field when you try to update them. A column of the identified table can be updated even though it is not part of the results table. That is, it can be specified in the FOR UPDATE OF clause even if it was not in the SELECT clause.
FOR FETCH ONLY Clause
This clause is used to indicate that there is no intention to update the table. When it is coded DB2 may perform a block FETCH to improve efficiency.
Read-Only Results Table
The results table is read-only if the SELECT statement includes the DISTINCT keyword, a UNION operator, a column function, a GROUP BY clause, a HAVING clause or an ORDER BY clause. It is also read-only if the FROM clause identifies a read-only view or identifies more than one table or view. If the results table is read-only then you cannot code the FOR UPDATE OF clause. This is a particular problem when a file of updates has a particular order to its records and you would like to code an ORDER BY clause so a merge can be performed.
WITH HOLD Option
A cursor may be declared WITH HOLD or WITHOUT HOLD.
Examples
EXEC SQL DECLARE C1 … CURSOR FOR SELECT … WITH HOLD … EXEC SQL DECLARE C1 … CURSOR FOR SELECT … WITHOUT HOLD …
The default is WITHOUT HOLD. If the cursor is declared WITHOUT HOLD, it is closed at commit (all locks released and the cursor is closed). if the cursor is declared WITH HOLD, the WITH HOLD designation prevents the cursor from being closed at commit. The cursor will remain open and positioned on the current row in the cursor at the time of commit. All page/row locks are released but the cursor position is maintained. The page/row lock is not required to maintain current position. A cursor declared with the WITH HOLD clause does not close after a COMMIT operation. A cursor that is not held closes after a COMMIT operation … the associated page/row locks are always released at COMMIT.
In Db2, an application program uses a cursor to point to one or more rows in a set of rows that are retrieved from a table. You can also use a cursor to retrieve rows from a result set that is returned by a stored procedure. Your application program can use a cursor to retrieve rows from a table.
You can retrieve and process a set of rows that satisfy the search condition of an SQL statement. When you use a program to select the rows, the program processes one or more rows at a time. the SELECT statement must be within a DECLARE CURSOR statement and cannot include an INTO clause. The DECLARE CURSOR statement defines and names the cursor, identifying the set of rows to retrieve with the SELECT statement of the cursor. This set of rows is referred to as the result table.
DB2 uses a technique known as cursor to make the rows from the results table available to the application program. A cursor provides the current row of the results table. When you use a cursor, the program can retrieve each row sequentially from the results table until end-of-data.
Read only cursor
The rows in the cursor can only be read, not updated. Read-only cursors are used when an application will only read data, not modify it. A cursor is considered read only if it is based on a read-only select-statement. You can define a read only cursor using either FOR FETCH ONLY clause or FOR READ ONLY clause in the DECLARE CURSOR statement.
Updatable cursor
The rows in the cursor can be updated. Updatable cursors are used when an application modifies data as the rows in the cursor are fetched. The specified query can only refer to one table or view. the cursor is defined using FOR UPDATE OF clause in the DECLARE CURSOR statement. The FOR UPDATE OF clause is followed by the column names that you want to update.
Ambiguous cursor
The cursor cannot be determined to be updatable or read only from its definition. This can happen when a dynamic SQL statement is encountered that could be used to change a cursor that would otherwise be considered read-only. when you do not specify FOR UPDATE OF or FOR FETCH ONLY or FOR READ ONLY clause, the cursor is treated as ambiguous cursor. an ambiguous cursor is treated as read only if the BLOCKING ALL option is specified when precompiling or binding. Otherwise, it is considered updatable.