DB2 FOR LUW / DB2FORLUW / 970 - DB2

If the view is defined as "select * from <table>" and has been 
invalidated due to a column dropped from the base table, the 
'DESCRIBE TABLE' command on the view will return incorrect 
result even after the view gets revalidated. This is caused by 
an inconsistency in the system catalog, specifically 
syscat.columns, where the record for the dropped column is not 
removed. 
 
However, this does not impact the result set return from the 
view and 'DESCRIBE select * from view' shows the correct result. 
 
Example: 
 
create table T1 ( c1 integer, c2 integer, c3 integer ) 
DB20000I  The SQL command completed successfully. 
 
create view V1 as select * from T1 
DB20000I  The SQL command completed successfully. 
 
alter table T1 drop column c3 
DB20000I  The SQL command completed successfully. 
 
select * from V1      (revalidates the view) 
 
C1          C2 
----------- ----------- 
 
  0 record(s) selected. 
 
describe select * from V1 
 
 Column Information 
 
 Number of columns: 2 
 
 SQL type              Type length  Column name 
Name length 
 --------------------  ----------- 
------------------------------  ----------- 
 497   INTEGER                   4  C1 
2 
 497   INTEGER                   4  C2 
2 
 
describe table V1 
 
                                Data type 
Column 
Column name                     schema    Data type name Length 
Scale Nulls 
------------------------------- --------- ------------------- 
---------- ----- ------ 
C1                                   SYSIBM    INTEGER 
4             0     Yes 
C2                                   SYSIBM    INTEGER 
4             0     Yes 
C3                                   SYSIBM    INTEGER 
4             0     Yes 
 
  3 record(s) selected.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* THE 'DESCRIBE TABLE' OUTPUT FOR A VIEW MAY SHOW INCORRECT    * 
* NUMBER                                                       * 
* OF COLUMNS EVEN AFTER THE VIEW IS REVALIDATED.               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to DB2 Fix Pack 6                                    * 
****************************************************************

Recreate the view using 'CREATE OR REPLACE VIEW'

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
 

First Fixed in DB2 V9.7 Fix Pack 6

Recreate the view using 'CREATE OR REPLACE VIEW'

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

27.10.2011
06.06.2012
06.06.2012

9.7.FP6