🗃 DB2

DB2 Cursors

Intermediate 🕑 25 min read 👁 0 views

What are Cursors?

Cursors allow you to process multiple rows returned by a SELECT statement one at a time. They act as pointers to a result set.

Cursor Operations

DECLARE - Define the cursor and its SELECT
OPEN - Execute the SELECT and create result set
FETCH - Retrieve rows one at a time
CLOSE - Release the cursor resources

Cursor Types

Read-only cursor - Cannot modify data
Updateable cursor - FOR UPDATE clause
WITH HOLD cursor - Survives COMMIT
Scrollable cursor - Move forward/backward

SQLCODE Values for Cursor

SQLCODE	Meaning
0	Successful
100	No more rows (end of data)
-501	Cursor not open
-502	Cursor already open
-507	Cursor not defined

Best Practices

Always close cursors when done
Check SQLCODE after each operation
Use WITH HOLD if needed across COMMITs
Avoid cursors when singleton SELECT works

Code Example

       WORKING-STORAGE SECTION.
       01 WS-EOF          PIC X VALUE 'N'.
          88 END-OF-DATA         VALUE 'Y'.
       01 WS-EMP-ID       PIC 9(6).
       01 WS-EMP-NAME     PIC X(30).
       01 WS-SALARY       PIC S9(7)V99 COMP-3.

       * DECLARE CURSOR
           EXEC SQL
               DECLARE EMP_CURSOR CURSOR FOR
               SELECT EMP_ID, EMP_NAME, SALARY
               FROM EMPLOYEE
               WHERE DEPT = :WS-DEPT
               ORDER BY EMP_NAME
           END-EXEC.

       * DECLARE WITH HOLD (SURVIVES COMMIT)
           EXEC SQL
               DECLARE EMP_HOLD_CURSOR CURSOR WITH HOLD FOR
               SELECT EMP_ID, EMP_NAME
               FROM EMPLOYEE
           END-EXEC.

       * DECLARE FOR UPDATE
           EXEC SQL
               DECLARE UPD_CURSOR CURSOR FOR
               SELECT EMP_ID, SALARY
               FROM EMPLOYEE
               WHERE DEPT = :WS-DEPT
               FOR UPDATE OF SALARY
           END-EXEC.

       PROCEDURE DIVISION.
       PROCESS-EMPLOYEES.
       * OPEN CURSOR
           EXEC SQL
               OPEN EMP_CURSOR
           END-EXEC.

           IF SQLCODE NOT = 0
               DISPLAY 'OPEN ERROR: ' SQLCODE
               STOP RUN
           END-IF.

       * FETCH LOOP
           PERFORM UNTIL END-OF-DATA
               EXEC SQL
                   FETCH EMP_CURSOR
                   INTO :WS-EMP-ID,
                        :WS-EMP-NAME,
                        :WS-SALARY
               END-EXEC

               EVALUATE SQLCODE
                   WHEN 0
                       PERFORM PROCESS-ROW
                   WHEN 100
                       SET END-OF-DATA TO TRUE
                   WHEN OTHER
                       DISPLAY 'FETCH ERROR: ' SQLCODE
                       SET END-OF-DATA TO TRUE
               END-EVALUATE
           END-PERFORM.

       * CLOSE CURSOR
           EXEC SQL
               CLOSE EMP_CURSOR
           END-EXEC.

       * UPDATE WHERE CURRENT OF
       UPDATE-WITH-CURSOR.
           EXEC SQL
               UPDATE EMPLOYEE
               SET SALARY = SALARY * 1.10
               WHERE CURRENT OF UPD_CURSOR
           END-EXEC.