DB2 FOR LUW / DB2FORLUW / B50 - DB2

Constant cursor variable revalidation fails with SQL0501N error
when depended objects are recreated.
The following scenario illustrates how this error comes up:

1. Create an alias for a depended object.
db2 "create table T1 (c1 int, c2 varchar(10))"
db2 create table T2 like t1
db2 "insert into T1 values (1, 'a')"
db2 "insert into T2 values (2, 'b')"
db2 create alias A1 for T1

2. Create a module contains constant cursor variable to be
revalidated.

db2 set sqlcompat PLSQL
db2 -tvf m1.sql

m1.sql
---------------------------
CREATE OR REPLACE MODULE M1
/

ALTER MODULE M1 ADD VARIABLE CUR1 CURSOR CONSTANT (
CURSOR WITH HOLD FOR SELECT * FROM A1)
/
ALTER MODULE M1 ADD VARIABLE ROW_CUR1 ANCHOR ROW OF CUR1
/

ALTER MODULE M1 PUBLISH PROCEDURE PROC1(OUT P1 VARCHAR(10))
/

ALTER MODULE M1 ADD PROCEDURE PROC1(OUT P1 VARCHAR(10))
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
    OPEN CUR1; --
    FETCH CUR1 INTO ROW_CUR1; --
    CLOSE CUR1; --

    SET P1 = ROW_CUR1.C2; --
END
/
---------------------------

3. Call the procedure to initialize the constant.
db2 connect to 
db2 "call M1.PROC1(?)"

  Value of output parameters
  --------------------------
  Parameter Name  : P1
  Parameter Value : a

  Return Status = 0

4. Change the target of the alias A1 to drive revalidation
against the constant cursor CUR1.
db2 drop alias A1
db2 create alias A1 for T2

5. Call the procedure again without resetting the database
connection.
db2 "call M1.PROC1(?)"
SQL0501N  The cursor specified in a FETCH statement or CLOSE
statement is not
open or a cursor variable in a cursor scalar function reference
is not open.
SQLSTATE=24501

6. The constant cursor is revalidated successfully once the
application disconnect from the database, for example:

db2 connect reset
db2 connect to 
db2 call M1.PROC1(?)
  Value of output parameters
  --------------------------
  Parameter Name  : P1
  Parameter Value : b

  Return Status = 0

This defect will address the issue so that constant cursor
variables revalidate successfully without resetting the database
connection.

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 version 11.5.5.0                              *
****************************************************************

Please reset the database connection mentioned in the error
description.
or
Please change the cursor from constant to local variable like as
below:

m1.sql
---------------------------
CREATE OR REPLACE MODULE M1
/

ALTER MODULE M1 PUBLISH TYPE TYPE_ROW_A1 AS ROW ANCHOR ROW A1
/
ALTER MODULE M1 PUBLISH TYPE TYPE_CUR_A1 AS TYPE_ROW_A1 CURSOR
/
ALTER MODULE M1 ADD VARIABLE CUR1 TYPE_CUR_A1
/
ALTER MODULE M1 ADD VARIABLE ROW_CUR1 TYPE_ROW_A1
/

ALTER MODULE M1 PUBLISH PROCEDURE PROC1(OUT P1 VARCHAR(10))
/

ALTER MODULE M1 ADD PROCEDURE PROC1(OUT P1 VARCHAR(10))
MODIFIES SQL DATA
LANGUAGE SQL
BEGIN
    SET CUR1 = CURSOR WITH HOLD FOR SELECT * FROM A1; --

    OPEN CUR1; --
    FETCH CUR1 INTO ROW_CUR1; --
    CLOSE CUR1; --
    SET P1 = ROW_CUR1.C2; --
END
/
---------------------------

****************************************************************
* USERS AFFECTED:                                              *
* ALL                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 version 11.5.5.0                              *
****************************************************************

First fixed in Db2 version 11.5.5.0

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

30.08.2021
30.08.2021
30.08.2021