DB2 Sensitive Dynamic Scrollable Cursor

Sensitive dynamic 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 using the cursor, the changes are immediately visible in the result table.

Committed INSERT, UPDATE or DELETE operations performed by the other applications are immediately visible in the result table even the already cursor opened.

Declaring a cursor as SENSITIVE DYNAMIC has the below results -

  • If other applications change the base table data, the cursor notices the newly changed data when it is committed.
  • The row's order can change after the application opens the cursor. Suppose the cursor SELECT statement contains an ORDER BY clause. Columns in the ORDER BY clause are updated after the cursor is opened. In that case, the order of the rows in the result table changes according to new changes.
  • Those changes are visible when the application executes positioned UPDATE and DELETE statements with the cursor.
  • Those changes are visible when the application executes all committed INSERT, UPDATE, or DELETE operations processed by other applications are visible.

Syntax -

EXEC SQL
	DECLARE cursor-name SENSITIVE DYNAMIC 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).

DYNAMIC -

  • All committed changes(done by current or other processes) to the base table are visible.

Example -

Input -

employee_details table

Scenario1 - Declare the SENSITIVE DYNAMIC SCROLL CURSOR for updating column manager_id of employee_details table.

Code -

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

Scenario2 - Declare the SENSITIVE DYNAMIC SCROLL CURSOR for updating all columns of the employee_details table.

Code -

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

More Examples