DB2 FOR LUW / DB2FORLUW / 970 - DB2

After recreating some objects that are part of a view 
definition, it is possible that the view may not be correctly 
invalidated due to an inconsistency in the SYSIBM.SYSVIEWDEP 
system catalog table. This may lead to an incorrect SQL0204N 
error being returned to the user. 
 
Moreover, selecting from the view can also yield incorrect 
results whilst packages using the old view definition remain in 
the package cache. 
 
The example below, shows the sequence of events that lead to the 
problem identified in this APAR. 
 
create view B as select * from (values (cast (null as int), cast 
(null as int))) as "B" (d1, d2); 
create table A (c1 int, c2 int); 
insert into A values (1,2); 
 
create view ALL as select * from A,B where c1 = 1; 
select * from ALL; 
drop table A; 
create view A as select * from (values (cast (null as int), cast 
(null as int))) as "A" (c1, c2); 
select * from ALL; 
drop view A; 
select * from ALL; 
create table A (c1 int, c2 int); 
insert into A values (1,2); 
 
 
Re-running the query at this point will show 0 rows being 
returned since view ALL is still referring to the old reference 
of view A. 
 
select * from ALL; 
 
After clearing the package cache (either by deactivating the 
database or executing "flush package cache dynamic"), the same 
query will return the SQL0204N error.

**************************************************************** 
* USERS AFFECTED:                                              * 
* all                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* When recreating tables that define a view, it is possible    * 
* that the view may return incorrect results as the view did   * 
* not get invalidated.                                         * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade the db2 server to v9.7 Fix Pack 4                    * 
****************************************************************

To avoid getting wrong results or ensure there are no active 
views subject to this problem, deactivate the database or run 
"flush package cache dynamic" to clear the database package 
cache. 
If there is a view that has the problem, it will cause SQL0204N 
to be returned from any statement accessing that view. To 
resolve the SQL0204N error, manually drop and recreate the view.

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 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
 

The problem has first been fixed in v9.7 Fix Pack 4

not known / see Local fix

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

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

19.11.2010
28.04.2011
28.04.2011

9.7.FP4