DB2 Sensitive Static Scrollable Cursor

Sensitive static scroll cursor is sensitive to 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 UPDATE or DELETE operations performed by the other applications are immediately visible in the result table even the already cursor opened. However, committed INSERT operations performed by other applications are not visible in the result table after the cursor is opened.

Declaring a cursor as SENSITIVE STATIC has the below outcomes -

  • Rows inserted into the original table are not added to the result table.
  • The row order does not change after the cursor opens. Suppose the cursor declaration contains an ORDER BY clause. The 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 does not change.
  • When the application executes positioned UPDATE and DELETE statements with the cursor, those changes are visible in the result table.
  • When a row of the result table is deleted from the original table, that row is no longer visible in the result table.
  • Changes made to the original table by other cursors or other application processes can be visible in the result table, depending on whether the FETCH statements you use with the cursor are FETCH INSENSITIVE or FETCH SENSITIVE statements.

Syntax -

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

STATIC -

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

Example -

Input -

employee_details table

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

Code -

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

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

Code -

  ...
   EXEC SQL 
	DECLARE E2 SENSITIVE STATIC 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