Cursor With Hold
Summary
The cursor should not close in some scenarios until the CLOSE CURSOR execution.
However, executing the COMMIT or SYNCPOINT commands will close all opened cursors.
To avoid this, WITH HOLD option is used while declaring the cursor.
WITH HOLD prevents the cursor from closing when the COMMIT operation is executed before the CLOSE CURSOR.
If WITH HOLD option specifies, COMMIT only commits the current unit of work without closing the cursor.
Syntax -
Declaring Cursor -
EXEC SQL
DECLARE cursor-name/cursor-variable-name
CURSOR WITH HOLD 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.
WITH HOLD -
- Specifies the cursor should not close when the COMMIT or SYNCPOINT executed.
Examples -
Input -
Scenario1 - Declare the WITH HOLD CURSOR for updating column manager_id of employee_details table.
Code -
EXEC SQL
DECLARE E1 CURSOR WITH HOLD FOR
SELECT EMP_NAME,
MANAGER_ID
FROM EMPLOYEE_DETAILS
FOR UPDATE OF MANAGER_ID
END-EXEC.Scenario2 - Declare the WITH HOLD CURSOR for updating all columns of the employee_details table.
Code -
EXEC SQL
DECLARE E2 CURSOR WITH HOLD 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 with hold cursor for designation "FRESHER" in department 1000 of employee_details table.
Code -
EXEC SQL
DECLARE E3 CURSOR WITH 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 WITH 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 -
Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CWHOLD.
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 WITH 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-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 OTHER
DISPLAY "DB2 FETCH ERROR: ", SQLCODE
END-EVALUATE.
FETCH-EMP-DETAILS-EXIT.
EXIT.
UPDATE-EMP-DETAILS.
COMPUTE WS-UPDATED-SAL = SALARY + 2000.
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.
IF WS-UCNT = 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 -
Table After Update -
