Summary -

In this topic, we described about the below sections -

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-

employee_details table

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

Code - DECLARE CURSOR
   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 - DECLARE CURSOR
   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 - DECLARE CURSOR
   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 -

Table Before Insert Output

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 -

SENSITIVE DYNAMIC SCROLL CURSOR Program Output

Table After Insert -

Table After Insert Output