DB2 Cursor Concepts
In DB2, a cursor is a database object used by applications to retrieve and manipulate multiple rows
of data from a result set one at a time. Cursors are essential when processing SQL queries that
return more than one row.
Cursor Lifecycle
1. DECLARE CURSOR
Define a cursor and associate it with a SELECT statement or a stored procedure result set.
2. OPEN CURSOR
Execute the query and establish the result set. Rows are now available for retrieval.
3. FETCH
Retrieve one row at a time from the cursor’s result set into host variables.
4. UPDATE/DELETE (Optional)
If the cursor is defined as updatable, the current row can be updated or deleted.
5. CLOSE CURSOR
Release the cursor and its associated resources.
Types of Cursors
Read-Only Cursor
Allows retrieval of rows but does not allow updates.
Updatable Cursor
Allows updates or deletes of the current row in the result set.
Scrollable Cursor
Enables movement forward and backward through the result set.
Non-Scrollable Cursor
Allows fetching rows only in sequential order from the start to the end.
Example Usage
Example in Embedded SQL (COBOL style):
EXEC SQL DECLARE C1 CURSOR FOR
SELECT EMPNO, LASTNAME FROM EMPLOYEE WHERE DEPTNO = 'A00';
EXEC SQL OPEN C1;
EXEC SQL FETCH C1 INTO :EMP-ID, :EMP-NAME;
... (process rows) ...
EXEC SQL CLOSE C1;
