Summary -

In this topic, we described about the below sections -

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-

employee_details table

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

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

Table Before Insert Output

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 -

SENSITIVE STATIC SCROLL CURSOR Program Output

Table After Update -

Table After Insert Output