DB2 FOR LUW / DB2FORLUW / 970 - 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 DB2 Version 9.7 GA through Fix Pack 1 serverson Linux,   * 
* Unix and Windows platforms utilizing theCREATE WITH ERROR    * 
* feature on a SQL procedurethat references a view.            * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* DROPPING A PROCEDURE THAT IS CREATED USING CREATE WITHERROR  * 
* MAY RESULT ORPHAN ROWS IN CATALOG                            * 
* TABLESYSIBM.SYSDEPENDENCIESWhen a procedure is created with  * 
* CREATE WITH ERROR andno specific name is specified, dropping * 
* the procedure willlead to orphan rows in the catalog         * 
* tableSYSIBM.SYSDEPENDENCIES.One symptom that may occur while * 
* dropping a view referencedin 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_FORCERun the following CREATE OR REPLACE            * 
* TWICE:CREATE OR REPLACE PROCEDURE myproc ()RESULT SETS       * 
* 1LANGUAGE SQLBEGINDECLARE 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@ThenCREATE  * 
* TABLE T1 (c1 int)CREATE VIEW V1 as select * from T1DROP VIEW * 
* V1 ====> sqlcode: -204To help identify if there is any       * 
* orphan rows inSYSIBM.SYSDEPEDENCIES, use the below           * 
* query:with routines as (select specificname,                 * 
* routineschemafromsyscat.routines) select routineschema,      * 
* specificname fromsyscat.routinedep d where not exists        * 
* (select r.specificname,r.routineschemafrom routines r where  * 
* d.routineschema =r.routineschema andd.specificname  =        * 
* r.specificname)Only V9.7 release is impacted.                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please upgrade to V9.7 FP2 or use the Circumvention          * 
****************************************************************

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)

DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

First fixed in V9.7 FP2

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC73391 
follow-up : 

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

31.03.2010
06.07.2010
06.07.2010

9.7.FP2