home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC73391 Status: Geschlossen

DROPPING A PROCEDURE THAT IS CREATED USING CREATE WITH ERROR MAY RESULT
ORPHAN ROWS IN CATALOG TABLE SYSIBM.SYSDEPENDENCIES

Produkt:
DB2 FOR LUW / DB2FORLUW / 980 - DB2
Problembeschreibung:
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.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 9.8 FP3 or Subsequent Fix Pack                * 
****************************************************************
Local-Fix:
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)
Lösung
Problem First Fixed in DB2 Version 9.8 Fix Pack 3
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
20.12.2010
16.11.2012
16.11.2012
Problem behoben ab folgender Versionen (IBM BugInfos)
9.8.FP3
Problem behoben lt. FixList in der Version