DB2 FOR LUW / DB2FORLUW / 970 - DB2

Whenever invalid view is converted to statsview then explicitly 
re-validated (select * form v1) and dropped , orphans entries in 
catalog PD gets created and subsequent drop table will fail with 
901. 
 
 
Here is how to repro: 
 
db2 "create table t1 (c1 varchar(5))"; 
db2 "create view v1 as select c1  from t1"; 
db2 "alter table t1 alter column c1 set data type varchar(50)"; 
db2 alter view v1 enable query optimization ; -- by now v1 is 
invalid 
db2 "select * from v1" -- explicit re-validation (necessary) 
db2 drop view v1 ; 
db2 drop table t1; -- will fail with 901 
 
DB21034E  The command was processed as an SQL statement because 
it was not a 
valid Command Line Processor command.  During SQL processing it 
returned: 
SQL0901N  The SQL statement failed because of a non-severe 
system error. 
Subsequent SQL statements can be processed.  (Reason "Dependent 
MST's packed 
Descriptor not found".)  SQLSTATE=58004

**************************************************************** 
* USERS AFFECTED:                                              * 
* LUW                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* move to db2_v97fp7 GA                                        * 
****************************************************************

The key idea is to not convert an "invalid" view to a statsview. 
Hence, before altering a view to statsview, make sure it's valid 
with the below query: 
 
"select valid from syscat.views where viewschema = <schema> and 
viewname = <name>" 
 
If valid = Y, then continue with the ALTER VIEW statement. 
Otherwise, to revalidate an invalid view, e.g. view name AB.CD, 
customer may 
 
     call SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('view', 'AB' , 
'CD'); 
 
or 
 
    call SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('view', NULL, 
NULL); 
 
to revalidate all views. After regular view gets out of invalid 
state, it will be safe to be converted to statview by ALTER VIEW 
statement.

DB2 Version 9.7 Fix Pack 7 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 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

db2_v97fp7_n120624, 
orphans entries will not be created in catalog PD and no 901 
reported

not known / see Local fix

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

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

13.06.2012
23.10.2012
23.10.2012

9.0.1