Summary -

In this topic, we described about the below sections -

Sensitive 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.

Declare scrollable cursors as SENSITIVE only if you want to see the latest data modified with the updates or deletes after the cursor opens.

The result table size, the order of the rows remains the same after the cursor opens i.e. -

  • Rows inserted after the cursor opened are not added to the result table.
  • Rows that are positioned updated/deleted after the cursor is opened are visible in the result table.
  • Also, deleted rows are not visible.

Syntax -

Declaring Cursor -
EXEC SQL
	DECLARE cursor-name/cursor-variable-name SENSITIVE 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 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.

Examples -

Input-

employee_details table

Scenario1 - Declare the sensitive scroll cursor for updating column manager_id of employee_details table.

Code - DECLARE CURSOR
   EXEC SQL 
	DECLARE E1 SENSITIVE SCROLL CURSOR FOR
     	SELECT EMP_NAME,
               MANAGER_ID
     	FROM   EMPLOYEE_DETAILS
        FOR    UPDATE OF MANAGER_ID
   END-EXEC.

Scenario2 - Declare the sensitive scroll cursor for updating all columns of the employee_details table.

Code - DECLARE CURSOR
   EXEC SQL 
	DECLARE E2 SENSITIVE 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 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 - Declare the sensitive scroll readonly cursor for designation "FRESHER" in departments 1000 and 2000 of the employee_details table.

Code - DECLARE CURSOR
   EXEC SQL 
	DECLARE E4 SCROLL CURSOR FOR
     	SELECT EMP_ID,
               EMP_NAME,
               DESIGNATION
     	FROM   EMPLOYEE_DETAILS 
     	WHERE  DESIGNATION = "FRESHER"
		AND    DEPT_ID IN (1000, 2000)
		ORDER  BY DEPT_ID ASC
     	FOR    READ ONLY 
   END-EXEC.

Practical Example -

Scenario - The below example describes how the sensitive scroll cursor is coded in the COBOL + DB2 program.

Requirement - Hike the salary of TESTER(s) by 1000.

Code -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID. CSENSITS.                                            
                                                                        
       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 CSR1 SENSITIVE SCROLL CURSOR FOR                
                SELECT EMP_ID,                                          
                       EMP_NAME,                                        
                       DESIGNATION,                                     
                       MANAGER_ID,                                      
                       SALARY                                           
                 FROM  EMPLOYEE_DETAILS                                 
                WHERE  DESIGNATION = 'TESTER'                           
                  AND  DEPT_ID = 2000                                   
                  FOR  UPDATE OF SALARY                                 
           END-EXEC.                                                    
                                                                        
        01 WS-VAR.                                                      
           05 WS-UCNT               PIC 9(02) VALUE ZEROES.             
           05 WS-UPDATED-SAL        PIC S9(5)V9(2) USAGE COMP-3.        
                                                                        
       PROCEDURE DIVISION.                                              
                                                                        
      * OPENING CURSOR                                                  
                                                                        
           EXEC SQL                                                     
                OPEN CSR1                                               
           END-EXEC.                                                    
                                                                        
      * FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE                  
                                                                        
           DISPLAY "FETCH CUSOR BEFORE UPDATE...".                      
           DISPLAY "-----------------------------------------------".   
                                                                        
           IF SQLCODE EQUAL ZERO                                        
              PERFORM FETCH-EMP-DETAILS                                 
                 THRU FETCH-EMP-DETAILS-EXIT                            
                UNTIL SQLCODE NOT EQUAL 0                               
           END-IF.                                                      
                                                                        
           DISPLAY "NO OF ROWS UPDATED:  ", WS-UCNT.                    
                                                                        
      * PLACING THE CURSOR AGAIN TO THE BEGINNING OF THE RESULT TABLE   
                                                                        
           PERFORM SCROLL-TO-START-OF-RT                                
              THRU SCROLL-TO-START-OF-RT-EXIT                           
                                                                        
      * FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE                  
                                                                        
           DISPLAY " ".                                                 
           DISPLAY "FETCH CUSOR AFTER UPDATE...".                       
           DISPLAY "-----------------------------------------------".   
                                                                        
           IF SQLCODE EQUAL ZERO                                        
              PERFORM FETCH-EMP-DETAILS-FB                              
                 THRU FETCH-EMP-DETAILS-FB-EXIT                         
                UNTIL SQLCODE NOT EQUAL 0                               
           END-IF.                                                      
                                                                        
      * CLOSING CURSOR                                                  
                                                                        
           EXEC SQL                                                     
                CLOSE CSR1                                              
           END-EXEC.                                                    
                                                                        
           STOP RUN.                                                    
                                                                        
       FETCH-EMP-DETAILS.                                               
                                                                        
      * FETCH CURSOR                                                    
                                                                        
           EXEC SQL                                                     
                FETCH CSR1                                              
                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                       
                    PERFORM UPDATE-EMP-DETAILS                          
                       THRU UPDATE-EMP-DETAILS-EXIT                     
               WHEN 100                                                 
                    CONTINUE                                            
               WHEN OTHER                                               
                    DISPLAY "DB2 ERROR:  ", SQLCODE                     
           END-EVALUATE.                                                
                                                                        
       FETCH-EMP-DETAILS-EXIT.                                          
           EXIT.                                                        
                                                                        
       UPDATE-EMP-DETAILS.                                              
                                                                        
           COMPUTE WS-UPDATED-SAL = SALARY + 1000                       
                                                                        
           EXEC SQL                                                     
                UPDATE EMPLOYEE_DETAILS                                 
                   SET SALARY = :WS-UPDATED-SAL                         
                 WHERE CURRENT OF CSR1                                  
           END-EXEC                                                     
                                                                        
           EVALUATE SQLCODE                                             
               WHEN ZERO                                                
                    COMPUTE WS-UCNT = WS-UCNT + 1                       
               WHEN 100                                                 
                    DISPLAY "UPDATING ROW NOT FOUND"                    
               WHEN OTHER                                               
                    DISPLAY "DB2 ERROR: ", SQLCODE                      
           END-EVALUATE.                                                
                                                                        
       UPDATE-EMP-DETAILS-EXIT.                                         
           EXIT.                                                        
                                                                        
       SCROLL-TO-START-OF-RT.                                           
                                                                        
      * FETCH CURSOR                                                    
                                                                        
           EXEC SQL                                                     
                FETCH BEFORE FROM CSR1                                  
           END-EXEC.                                                    
                                                                        
           EVALUATE SQLCODE                                             
               WHEN ZERO                                                
               WHEN 100                                                 
                    CONTINUE                                            
               WHEN OTHER                                               
                    DISPLAY "DB2 ERROR:  ", SQLCODE                     
           END-EVALUATE.                                                
                                                                        
       SCROLL-TO-START-OF-RT-EXIT.                                      
           EXIT.                                                        
                                                                        
       FETCH-EMP-DETAILS-FB.                                            
                                                                        
      * FETCH CURSOR                                                    
                                                                        
           EXEC SQL                                                     
                FETCH CSR1                                              
                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-FB-EXIT.                                       
           EXIT.                                                        
                                                                        
**************************** Bottom of Data ****************************
SENSITIVE SCROLL CURSOR Program Code

Output -

SENSITIVE SCROLL CURSOR Program Output