|
Problem IC79500
|
Status: Closed |
THE 'DESCRIBE TABLE' OUTPUT FOR A VIEW MAY SHOW INCORRECT NUMBER OF COLUMNS EVEN AFTER THE VIEW IS REVALIDATED. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
****************************************************************
* 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 *
**************************************************************** |
| Local Fix: |
Recreate the view using 'CREATE OR REPLACE VIEW' |
| available fix packs: |
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
|
| Solution |
First Fixed in DB2 V9.7 Fix Pack 6 |
| Workaround |
Recreate the view using 'CREATE OR REPLACE VIEW' |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.10.2011
06.06.2012
06.06.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
