Sensitive Dynamic Scrollable Cursor
Summary
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.
INSENSITIVE FETCH is not allowed in Sensitive Dynamic Scroll Cursor and only SENSITIVE FETCH is allowed.
Declaring a cursor as SENSITIVE DYNAMIC has the below results -
- The result table size and contents can change with each and every FETCH.
- If other applications change the base table data, the cursor notices the newly changed data when it is committed.
- The rows 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.
- The cursor needs no temporary result table because the FETCH statement executes against the base table.
Syntax -
Declaring Cursor -
EXEC SQL
DECLARE cursor-name/cursor-variable-name SENSITIVE DYNAMIC 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] 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 committed changes (inserts, deletes and updates) are visible in the result table and changes in the order of the rows.
Examples -
Input -
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.Scenario3 - Declare the sensitive scroll readonly cursor for designation "FRESHER" in department 1000 of employee_details table.
Code -
EXEC SQL
DECLARE E3 SENSITIVE DYNAMIC 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.Practical Example -
Scenario - The below example describes how the SENSITIVE DYNAMIC SCROLL CURSOR is coded in the COBOL + DB2 program.
Requirement - Insert employee after the Sensitive dynamic cursor opened.
Table Before INSERT -
Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CSDSCROL.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE EMPLDET
END-EXEC.
* DECLARING UPDATE CURSOR
EXEC SQL
DECLARE CSR_SDSCROLL SENSITIVE DYNAMIC 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_SDSCROLL
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_SDSCROLL
END-EXEC.
STOP RUN.
FETCH-EMP-DETAILS.
* FETCH CURSOR
EXEC SQL
FETCH CSR_SDSCROLL
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 ****************************Program Output -
Table After Insert -
