Sensitive Static Scrollable Cursor
Summary
Sensitive static scroll cursor is the cursor that 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 opened.
The SENSITIVE/INSENSITIVE FETCH is allowed in Sensitive Static Cursor.
Declaring a cursor as SENSITIVE STATIC has the below outcomes -
- The result table size does not grow after the cursor opens.
- Rows inserted into the original table are not added to the result table.
- The rows 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 no longer satisfies the SELECT statement used in the cursor declaration, that row is no longer 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 -
Declaring Cursor -
EXEC SQL
DECLARE cursor-name/cursor-variable-name SENSITIVE STATIC SCROLL
CURSOR FOR select-statement
FOR UPDATE OF columns-list/FOR READ ONLY
END-EXEC.Opening Cursor -
EXEC SQL
OPEN cursor-name/cursor-variable-name;
END-EXEC.Fetching Cursor -
EXEC SQL
FETCH [SENSITIVE/INSENSITIVE] 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.
SENSITIVE -
- Specifies the fetch is SENSITIVE.
- It needs to be specify SENSITIVE explicitly.
- When FETCH SENSITIVE -
- All updates and deletes are visible in the result table.
- Inserts made by other processes are not visible in the result table.
INSENSITIVE -
- Speciifies the fetch is INSENSITIVE.
- If not specified explicitly, it is default.
- When FETCH SENSITIVE -
- Only positioned updates and deletes made by the cursor are visible in the result table.
Examples -
Input -
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.Scenario3 - Declare the sensitive scroll readonly cursor for designation "FRESHER" in department 1000 of employee_details table.
Code -
EXEC SQL
DECLARE E3 SENSITIVE STATIC SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = "FRESHER"
AND DEPT_ID = 1000
FOR READ ONLY
END-EXEC.Scenario4 - SENSITIVE FETCH for getting emp_id, emp_name on sensitive scroll readonly cursor.
Code -
DECLARE CURSOR
EXEC SQL
DECLARE E4 SENSITIVE STATIC SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = "FRESHER"
AND DEPT_ID IN (1000, 2000)
ORDER BY DEPT_ID ASC
FOR READ ONLY
END-EXEC.FETCH -
EXEC SQL
FETCH SENSITIVE E4
INTO :HV-EMP-ID,
:HV-EMP-NAME
END-EXEC.Scenario5 - INSENSITIVE FETCH for getting emp_id, emp_name on sensitive scroll readonly cursor.
Code -
DECLARE CURSOR
EXEC SQL
DECLARE E4 SENSITIVE STATIC SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = "FRESHER"
AND DEPT_ID IN (1000, 2000)
ORDER BY DEPT_ID ASC
FOR READ ONLY
END-EXEC.FETCH -
EXEC SQL
FETCH INSENSITIVE E4
INTO :HV-EMP-ID,
:HV-EMP-NAME
END-EXEC.Practical Example -
Scenario - The below example describes how the SENSITIVE STATIC SCROLL CURSOR is coded in the COBOL + DB2 program.
Requirement - Insert an employee after the Sensitive static cursor opened.
Table Before Update -
Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CSSSCROL.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE EMPLDET
END-EXEC.
* DECLARING UPDATE CURSOR
EXEC SQL
EXEC SQL
DECLARE CSR_SSSCROLL SENSITIVE STATIC SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION,
MANAGER_ID,
SALARY
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = 'TESTER'
AND DEPT_ID = 2000
FOR READ ONLY
END-EXEC.
PROCEDURE DIVISION.
* OPENING CURSOR
EXEC SQL
OPEN CSR_SSSCROLL
END-EXEC.
* INSERTING A EMPLOYEE THAT MATCHES TO THE CURSOR CONDITION
PERFORM INSERT-EMP-DETAILS
THRU INSERT-EMP-DETAILS-EXIT.
* 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 CSR_SSSCROLL
END-EXEC.
STOP RUN.
FETCH-EMP-DETAILS.
* FETCH CURSOR
EXEC SQL
FETCH CSR_SSSCROLL
INTO :EMP-ID,
:EMP-NAME,
:DESIGNATION,
:MANAGER-ID
:MANAGER-ID-NI,
:SALARY
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY EMP-ID " : " EMP-NAME " : " DESIGNATION
- " : " MANAGER-ID " : " SALARY
WHEN 100
CONTINUE
WHEN OTHER
DISPLAY "DB2 ERROR: ", SQLCODE
END-EVALUATE.
FETCH-EMP-DETAILS-EXIT.
EXIT.
INSERT-EMP-DETAILS.
* INSERT EMPLOYEE WITH EMP_ID 10
EXEC SQL
INSERT INTO EMPLOYEE_DETAILS
VALUES (10, 'EMPLOYEE10', 'TESTER', 'EMPLOYEE4',
'2019-10-08', 15000, 2000)
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY "ROW INSERTED SUCCESSFULLY"
WHEN OTHER
DISPLAY "DB2 INSERT ERROR: ", SQLCODE
END-EVALUATE.
INSERT-EMP-DETAILS-EXIT.
EXIT.
**************************** Bottom of Data ****************************Output -
Table After Update -
