Summary -

In this topic, we described about the below sections -

Executing the COMMIT or SYNCPOINT commands will close all opened cursors.

To avoid this, WITH HOLD option is used while declaring the cursor.

If WITH HOLD option is not specified, the cursor will close automatically. These cursors are called as cursors WITHOUT HOLD.

WITHOUT HOLD option is default and no need to specify explicitly.

Syntax -

Declaring Cursor -
EXEC SQL
	DECLARE cursor-name/cursor-variable-name 
	    CURSOR [WITHOUT HOLD] FOR select-statement
	    [FOR UPDATE OF column-list]/[READ ONLY]
END-EXEC.

OR

EXEC SQL
	DECLARE cursor-name/cursor-variable-name 
	    CURSOR FOR select-statement
	    [FOR UPDATE OF column-list]/[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.

WITHOUT HOLD -

  • Specifies the cursor can close when the COMMIT or SYNCPOINT executed.

Examples -

Input-

Table Before Update

Scenario1 - Declare the WITHOUT HOLD CURSOR for updating column manager_id of employee_details table.

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

Scenario2 - Declare the WITHOUT HOLD CURSOR for updating all columns of the employee_details table.

Code - DECLARE CURSOR -
   EXEC SQL 
	DECLARE E2 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 without hold cursor for designation "FRESHER" in department 1000 of employee_details table.

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

Practical Example -

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

Requirement - Increasing the salary of every TESTER by 2000 and save the changes after the second row got updated without closing the cursor.

Table Before Update -

Table Before Update

Code -

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
       IDENTIFICATION DIVISION.                                         
       PROGRAM-ID. CWOHOLD.                                             
       AUTHOR. MTH.                                                     
                                                                        
       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  CURSOR WITHOUT HOLD 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-ROW-COUNT          PIC S9(9) USAGE COMP.               
           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                  
                                                                        
           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 CSR1                                              
           END-EXEC.                                                    
                                                                        
           DISPLAY "NO OF ROWS UPDATED:  ", WS-UCNT.                    
                                                                        
           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 -501                                                
                    DISPLAY "FETCH ERROR: CURSOR ALREADY CLOSED"        
               WHEN OTHER                                               
                    DISPLAY "DB2 FETCH 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 UPDATE ERROR: ", SQLCODE               
           END-EVALUATE.                                                
                                                                        
           IF WS-UCNT EQUAL 2                                           
              PERFORM COMMIT-EMP-DETAILS                                
                 THRU COMMIT-EMP-DETAILS-EXIT                           
           END-IF.                                                      
                                                                        
       UPDATE-EMP-DETAILS-EXIT.                                         
           EXIT.                                                        
                                                                        
       COMMIT-EMP-DETAILS.                                              
                                                                        
           EXEC SQL                                                     
                COMMIT                                                  
           END-EXEC.                                                    
                                                                        
           EVALUATE SQLCODE                                             
               WHEN ZERO                                                
                    DISPLAY "COMMIT PERFORMED SUCCESSFULLY"             
               WHEN OTHER                                               
                    DISPLAY "DB2 COMMIT ERROR: ", SQLCODE               
           END-EVALUATE.                                                
                                                                        
       COMMIT-EMP-DETAILS-EXIT.                                         
           EXIT.                                                        
                                                                        
**************************** Bottom of Data ****************************

Output -

WITH HOLD CURSOR Program Output

Table After Update -

Table After Update Output