DB2 Sensitive Scrollable Cursor

Sensitive scroll cursor is the cursor that is sensitive to insert, update, or delete operations performed on the database table after generating the result table. i.e., after opening the cursor. For example, when the positioned UPDATE or DELETE is performed, the changes are immediately visible in the result table.

Declare scrollable cursors as SENSITIVE only if we want to see the latest data modified with the updates or deletes after the cursor opens. The result table size, the order of the rows remains the same after the cursor opens i.e., -

• Rows inserted after the cursor is opened are not added to the result table.
• Rows that are positioned updated/deleted after the cursor is opened are visible in the result table.
• Also, deleted rows are not visible.

Syntax -

EXEC SQL
	DECLARE cursor-name SENSITIVE SCROLL
	    CURSOR FOR 
		select-statement
END-EXEC.

SENSITIVE -

• Specifies the cursor is sensitive to insert, update, or delete operations performed on the table after the result table is created.
• This option is the default option. For example, when the positioned UPDATE or DELETE is performed using the cursor, the changes are immediately visible in the result table.

SCROLL -

• SCROLL specifies the cursor is scrollable.
• If the cursor is scrollable, the cursor can scroll forward, backward, and repositioned at the beginning, at the end, or the relative offset position.
• Scrollable cursors are especially useful for screen-based applications (CICS screens with page-up and page-down logic).

Example -

Input -

Scenario1 - Declare the sensitive scroll cursor for updating column manager_id of employee_details table.

Code -

   EXEC SQL 
	DECLARE E1 SENSITIVE SCROLL CURSOR FOR
     	SELECT EMP_NAME,
               MANAGER_ID
     	FROM   EMPLOYEE_DETAILS
        FOR    UPDATE OF MANAGER_ID
   END-EXEC.

Scenario2 - Declare the sensitive scroll cursor for updating all columns of the employee_details table.

Code -

   EXEC SQL 
	DECLARE E2 SENSITIVE SCROLL CURSOR FOR
     	SELECT EMP_ID,
               EMP_NAME,
               DESIGNATION,
               MANAGER_ID,
               DATE_OF_HIRE,
               SALARY,
               DEPT_ID
     	FROM   EMPLOYEE_DETAILS 
     	FOR UPDATE 
   END-EXEC.

---