I confirmed the following syntax in the DB2 LUW v11.5 manual (we are running DB2 LUW v11.5.9). And apparently this syntax has been supported at least since v9.7! This is in a stored procedure and, yes, C1 is declared as a cursor.
OPEN C1;
If ( C1 IS OPEN ) Then
So, why does the IF statement generate the following error message?
Error: "C1" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.33.31
Color me confused!
There are 2 different techniques of cursor processing depending on what you declared: cursor or cursor variable.
IS [NOT] OPEN
is applicable to cursor variables only.
Look at the examples below.
(1) Processing with a cursor variable
BEGIN
DECLARE v_int INT;
DECLARE c1 CURSOR; -- cursor variable declaration
SET c1 = CURSOR FOR VALUES (1), (2);
OPEN c1;
IF (c1 IS OPEN) THEN -- Not really needed here, just for demo
L1: LOOP
FETCH c1 INTO v_int;
IF (c1 IS NOT FOUND) THEN LEAVE L1; END IF;
-- do something useful here
CALL DBMS_OUTPUT.PUT_LINE('v_int: ' || v_int);
END LOOP L1;
CLOSE c1;
END IF;
END
@
(2) Processing with a cursor (the FOR
clause after the CURSOR
one in DECLARE
)
BEGIN
DECLARE v_int INT;
DECLARE SQLSTATE CHAR(5);
DECLARE c1 CURSOR FOR VALUES (1), (2); -- cursor declaration
OPEN c1;
L1: LOOP
FETCH c1 INTO v_int;
IF SQLSTATE = '02000' THEN LEAVE L1; END IF;
-- do something useful here
CALL DBMS_OUTPUT.PUT_LINE('v_int: ' || v_int);
END LOOP L1;
CLOSE c1;
END
@