|
Problem IC77451
|
Status: Closed |
QUERY AGAINST A VIEW MAY RETURN SQL0901N: "COLUMN NUMBER OUT OF RANGE" AFTER AN ALTER TO DROP A COLUMN OF ONE ITS BASE TABLE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All DB2 users. *
****************************************************************
* PROBLEM DESCRIPTION: *
* See error description above *
****************************************************************
* RECOMMENDATION: *
* Upgrade to 9.7 FP5 or higher *
**************************************************************** |
| Local Fix: |
Dropping and recreating the view should resolve the sql0901n
error. |
| available fix packs: |
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
|
| Solution |
Problem Fixed in DB2 Version 9.7 Fix Pack 5 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 11.07.2011
13.03.2012
13.03.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
