Cursor with COBOL

A complete COBOL program using a cursor follows the same four steps: DECLARE in Working-Storage, then OPEN, FETCH in a loop, and CLOSE in the Procedure Division.

Full program example - read all departments

• The program below declares cursor C1 on the DEPT table, opens it, fetches each row into a group item, displays it, and closes the cursor at end of data.
• Example:
  
  IDENTIFICATION DIVISION. PROGRAM-ID. CURSEL. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. 01 DCLDEPT. 02 DNO PIC S9(4) COMP. 02 DNAME PIC X(10). EXEC SQL DECLARE C1 CURSOR FOR SELECT DNO, DNAME FROM DEPT END-EXEC. PROCEDURE DIVISION. PERFORM OPEN-PARA. PERFORM FETCH-PARA UNTIL SQLCODE NOT = 0. STOP RUN. OPEN-PARA. EXEC SQL OPEN C1 END-EXEC. IF SQLCODE NOT = 0 DISPLAY "OPEN FAILED " SQLCODE STOP RUN END-IF. FETCH-PARA. EXEC SQL FETCH C1 INTO :DCLDEPT END-EXEC. IF SQLCODE = 0 DISPLAY DNO " " DNAME ELSE IF SQLCODE = 100 PERFORM CLOSE-PARA ELSE DISPLAY "FETCH FAILED " SQLCODE STOP RUN END-IF END-IF. CLOSE-PARA. EXEC SQL CLOSE C1 END-EXEC.

Key parts explained

• EXEC SQL INCLUDE SQLCA END-EXEC - brings in the SQL communication area so the program can read SQLCODE after every statement.
• 01 DCLDEPT - host variables. In real programs these are generated by DCLGEN so they match the table columns exactly.
• DECLARE C1 CURSOR FOR ... - in Working-Storage. It names the cursor and stores the SELECT; nothing is executed here.
• OPEN-PARA - runs once before the loop. Checks SQLCODE and stops if OPEN fails.
• FETCH-PARA - runs in a loop. On SQLCODE 0 it processes the row; on 100 it closes the cursor; on anything else it reports the error and stops.
• CLOSE-PARA - releases the cursor when the FETCH loop reaches end of data.

From source to running program

• The COBOL precompiler extracts the SQL statements into a DBRM (Data Base Request Module).
• The DBRM is bound into a plan or package with the BIND process before the program can run.
• If the timestamp in the load module and the plan do not match, the program fails with SQLCODE -818 - recompile and rebind together.
• Host variables (starting with a colon, like :DCLDEPT) are the bridge between COBOL and DB2 - every value passed to or from SQL goes through them.