DB2 FOR LUW / DB2FORLUW / 970 - DB2

SELECT from a view may cause SQL0901N error as following: 
 
 
SQL0901N  The SQL statement failed because of a non-severe 
system error. 
Subsequent SQL statements can be processed.  (Reason "column 
number out of range".)  SQLSTATE=58004 
 
The problem occurs following an ALTER TABLE with DROP COLUMN 
statement on one of the view's base table. ALTER TABLE does not 
update the total number of columns in the definition/packed 
descriptor of its dependent view. The two conditions to hit this 
APAR is: 
 
1) the view must be based on more than 1 table 
2) depending on which base table has column dropped, the 
subsequent query may or may not hit the -901 column out of range 
error. 
 
Here are the simple steps to reproduce the problem: 
 
- create table t1(c1 int, c2 int) 
- create table t2(c1 int, c2 int, c3 int, c4 int, c5 int) 
- create view v1 (a,b) as select t1.c1, t2.c2 from t1 t1, t2 t2 
- db2cat -d viva -s newton -n v1 -o v1.pd 
 
(output of db2cat command as above) 
*********************************** 
Base tables information      : 
  FID                        : 5 
  TID                        : 4 
  No. cols table 1st created  : 5 
 
  FID                        : 4 
  TID                        : 4 
  No. cols table 1st created  : 2 
*********************************** 
 
Note: TID/FID (4,5) is T2 and (4,4) is T1 
 
 
- alter table t1 drop column c2 
 
(output of db2cat command as above) 
*********************************** 
Base tables information      : 
  FID                        : 5 
  TID                        : 4 
  No. cols table 1st created  : 5 
 
  FID                        : 4 
  TID                        : 4 
  No. cols table 1st created  : 2 
*********************************** 
 
The SELECT query from the above view may not necessarily fail. 
But, the db2cat output shows the number of columns in T1 was not 
updated.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 users.                                               * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See error description above                                  * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to 9.7 FP5 or higher                                 * 
****************************************************************

Dropping and recreating the view should resolve the sql0901n 
error.

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
 

Problem Fixed in DB2 Version 9.7 Fix Pack 5

keiner bekannt / siehe Local-Fix

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

11.07.2011
13.03.2012
13.03.2012

9.7.FP5