Summary -

In this topic, we described about the below sections -

Insensitive scroll cursor is the cursor that is not 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 or INSERT is performed using the cursor, the changes are not visible in the result table.

SENSITIVE FETCH is not allowed in insensitive scroll cursor and only INSENSITIVE FETCH is allowed.

The size, rows order, and the values for each row of the result table do not change after the cursor opens.

Declaring a cursor as INSENSITIVE has the below results -

  • Rows inserted into the original table are not added to the result table after the cursor opens.
  • The result table is read-only.
  • FOR UPDATE clause is not allowed in the cursor. i.e., the cursor can't use for the positioned update or delete operations.

Syntax -

Declaring Cursor -
EXEC SQL
	DECLARE cursor-name/cursor-variable-name INSENSITIVE SCROLL
	    CURSOR FOR select-statement
	    FOR READ ONLY
END-EXEC.
Opening Cursor -
EXEC SQL
	OPEN cursor-name/cursor-variable-name;
END-EXEC.
Fetching Cursor -
EXEC SQL
	FETCH [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.

INSENSITIVE -

  • Specifies the fetch is INSENSITIVE.
  • When FETCH INSENSITIVE -
    • No changes to the original table are visible in the result table.
    • Positioned UPDATE and DELETE statements are not allowed.

Examples -

Input-

employee_details table

Scenario1 - Declare the INSENSITIVE SCROLL CURSOR for reading employee_details table.

Code - DECLARE CURSOR
   EXEC SQL 
	DECLARE E1 INSENSITIVE SCROLL CURSOR FOR
     	SELECT EMP_NAME,
               MANAGER_ID
     	FROM   EMPLOYEE_DETAILS
        FOR    READ ONLY
   END-EXEC.

Scenario2 - Declare the insensitive scroll readonly cursor for designation "FRESHER" in department 1000 of employee_details table.

Code - DECLARE CURSOR
   EXEC SQL 
	DECLARE E2 INSENSITIVE SCROLL CURSOR FOR
     	SELECT EMP_ID,
               EMP_NAME,
               DESIGNATION
     	FROM   EMPLOYEE_DETAILS 
     	WHERE  DESIGNATION = "FRESHER"
		AND    DEPT_ID = 1000
     	FOR    READ ONLY 
   END-EXEC.

Scenario3 - INSENSITIVE FETCH for getting emp_id, emp_name on insensitive scroll readonly cursor.

Code - DECLARE CURSOR
   EXEC SQL 
	DECLARE E3 INSENSITIVE 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 E3
     	 INTO :HV-EMP-ID,
              :HV-EMP-NAME
   END-EXEC.

Practical Example -

Scenario - The below example describes how the INSENSITIVE SCROLL CURSOR is coded in the COBOL + DB2 program.

Requirement - Insert employee after the insensitive cursor opened.

Table Before Update -

Table Before Insert Output

Code -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID. CISENSIT.                                            
                                                                        
       ENVIRONMENT DIVISION.                                            
       DATA DIVISION.                                                   
       WORKING-STORAGE SECTION.                                         
                                                                        
           EXEC SQL                                                     
             INCLUDE SQLCA                                              
           END-EXEC.                                                    
                                                                        
           EXEC SQL                                                     
             INCLUDE EMPLDET                                            
           END-EXEC.                                                    
                                                                        
      * DECLARING READ ONLY CURSOR                                      
                                                                        
           EXEC SQL                                                     
               DECLARE CSR_INSCROLL INENSITIVE 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 -

INSENSITIVE SCROLL CURSOR Program Output

Table After Update -

Table After Insert Output