Summary -
In this topic, we described about the below sections -
The read-only cursor is a cursor that is used to perform only read operations on the table or result table.
FOR FETCH ONLY option used to declare READ ONLY Cursors.
Positioned UPDATE and positioned DELETE operations not allowed on read-only cursors.
The read-only cursor has the following advantages -
- It improves the performance of FETCH operations.
- No blocking and avoids exclusive locks.
- It prevents some types of deadlocks.
"FOR FETCH ONLY" can specify as a synonym for "FOR READ ONLY".
Syntax -
Declaring Cursor - EXEC SQL DECLARE cursor-name/cursor-variable-name CURSOR FOR select-statement FOR FETCH ONLY; END-EXEC. Opening Cursor - EXEC SQL OPEN cursor-name/cursor-variable-name; END-EXEC. Fetching Cursor - EXEC SQL FETCH cursor-name/cursor-variable-name INTO :hv1, :hv2, . . :hvn; END-EXEC. Closing Cursor - EXEC SQL CLOSE cursor-name/cursor-variable-name; END-EXEC.
cursor-name -
- Cursor-name is the name of the cursor used in an application program to refer to the cursor.
- The cursor-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
cursor-variable-name -
- Cursor-variable-name is a working-storage variable that contains the cursor name used in an application program to refer to the cursor.
- The cursor-variable-name should be unique in the entire application program.
- The max length of the cursor-name is 30 characters for the cursors declared WITH RETURN.
- The max length of the cursor-name is 128 characters for the standard cursors.
Examples -
Input-

Scenario1 - Declare the cursor for retrieving all employee names from the employee_details table.
Code- EXEC SQL DECLARE E1 CURSOR FOR SELECT EMP_NAME FROM EMPLOYEE_DETAILS END-EXEC.
Scenario2 - Declare the read-only cursor for retrieving all employee names from the employee_details table.
Code- EXEC SQL DECLARE E2 CURSOR FOR SELECT EMP_NAME FROM EMPLOYEE_DETAILS FOR FETCH ONLY END-EXEC.
Scenario3 - Declare the read-only cursor for retrieving all employee names from highest salary to lowest.
Code- EXEC SQL DECLARE E2 CURSOR FOR SELECT EMP_NAME FROM EMPLOYEE_DETAILS ORDER BY SALARY DESC FOR FETCH ONLY END-EXEC.
Scenario4 - Declare the read-only cursor for retrieving the sum of salaries at the designation level.
Code- EXEC SQL DECLARE E2 CURSOR FOR SELECT DESIGNATION, SUM(SALARY) SALARIES_TOTAL FROM EMPLOYEE_DETAILS GROUP BY DESIGNATION FOR FETCH ONLY END-EXEC.
Scenario5 - Declare the read-only cursor for retrieving the sum of salaries > 18000 at designation level.
Code- EXEC SQL DECLARE E2 CURSOR FOR SELECT DESIGNATION, SUM(SALARY) SALARIES_TOTAL FROM EMPLOYEE_DETAILS GROUP BY DESIGNATION HAVING SUM(SALARY) > 18000 FOR FETCH ONLY END-EXEC.
Practical Example -
Scenario - The below example describes how the read-only cursor is coded in the COBOL + DB2 program.
Requirement - Display employee_details table data.
Code -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- ***************************** Top of Data ****************************** IDENTIFICATION DIVISION. PROGRAM-ID. CREADONL. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC SQL INCLUDE EMPLDET END-EXEC. * DECLARING READONLY CURSOR EXEC SQL DECLARE CSR1 CURSOR FOR SELECT EMP_ID, EMP_NAME, DESIGNATION, MANAGER_ID, DATE_OF_HIRE, SALARY, DEPT_ID FROM EMPLOYEE_DETAILS FOR FETCH ONLY END-EXEC. PROCEDURE DIVISION. * OPENING CURSOR EXEC SQL OPEN CSR1 END-EXEC. * FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE IF SQLCODE EQUAL ZERO PERFORM FETCH-EMP-DETAILS THRU FETCH-EMP-DETAILS-EXIT UNTIL SQLCODE NOT EQUAL 0 END-IF. * CLOSING CURSOR EXEC SQL CLOSE CSR1 END-EXEC. STOP RUN. FETCH-EMP-DETAILS. * FETCH CURSOR EXEC SQL FETCH CSR1 INTO :EMP-ID, :EMP-NAME, :DESIGNATION, :MANAGER-ID :MANAGER-ID-NI, :DATE-OF-HIRE, :SALARY, :DEPT-ID END-EXEC. EVALUATE SQLCODE WHEN ZERO DISPLAY EMP-ID, "|", EMP-NAME, "|", DESIGNATION, "|", MANAGER-ID, "|", DATE-OF-HIRE, "|", SALARY, "|", DEPT-ID WHEN 100 CONTINUE WHEN OTHER DISPLAY "DB2 ERROR: ", SQLCODE END-EVALUATE. FETCH-EMP-DETAILS-EXIT. EXIT. **************************** Bottom of Data ****************************
Output -
