DB2 Read Only Cursor

A read-only cursor is a type of cursor that allows us to retrieve the data but does not allow updates or deletions of the rows fetched. This cursor type is used when the program only needs to read data without changing it, making it simpler and typically faster than updateable cursors.

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.

Syntax -

EXEC SQL
	DECLARE cursor-name 
	    CURSOR FOR select-statement
	    FOR FETCH ONLY
END-EXEC.

Example -

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.
   ...

---