DB2 FOR LUW / DB2FORLUW / 980 - DB2

When a procedure is created with CREATE WITH ERROR and no 
specific name is specified, dropping the procedure will lead to 
orphan rows in the catalog table SYSIBM.SYSDEPENDENCIES. 
One symptom that may occur while dropping a view referenced in a 
procedure created as above is an SQL0204N error. 
 
Here is the scenario to reproduce the symptom seen: 
 
update db cfg using AUTO_REVAL DEFERRED_FORCE 
 
Run the following CREATE OR REPLACE TWICE: 
 
CREATE OR REPLACE PROCEDURE myproc () 
RESULT SETS 1 
LANGUAGE SQL 
BEGIN 
   DECLARE i1 INT DEFAULT 1; 
   DECLARE i2 INT DEFAULT 0; 
   DECLARE c1 CURSOR FOR SELECT c1 FROM v1; 
   OPEN c1; 
   FETCH c1 INTO i1; 
   SET i2 = i1 + 1; 
   CLOSE c1; 
END@ 
 
Then 
CREATE TABLE T1 (c1 int) 
 
CREATE VIEW V1 as select * from T1 
DROP VIEW V1 ====> sqlcode: -204 
 
To help identify if there is any orphan rows in 
SYSIBM.SYSDEPEDENCIES, use the below query: 
 
with routines as (select specificname, routineschema from 
syscat.routines) select routineschema, specificname from 
syscat.routinedep d where not exists (select r.specificname, 
r.routineschema from routines r where d.routineschema = 
r.routineschema and d.specificname  = r.specificname) 
 
 
 
Only V9.7 release is impacted.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 9.8 FP3 or Subsequent Fix Pack                * 
****************************************************************

Create a dummy procedure with the specific name returned by the 
above query, then retry the DROP VIEW statement. 
Dropping the dummy procedure will help remove the orphan row in 
SYSIBM.SYSDEPENDENCIES. 
 
Another alternative is to contact IBM Support to remove the 
orphan row(s)

Problem First Fixed in DB2 Version 9.8 Fix Pack 3

not known / see Local fix

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

20.12.2010
16.11.2012
16.11.2012

9.8.FP3