DB2 FOR LUW / DB2FORLUW / 970 - DB2

A database configuration parameter, AUTO_REVAL, is introduced 
in v9.7, and is set to DEFERRED by default for new databases 
created in v9.7. When AUTO_REVAL is set to anything that is not 
DISABLED, ALTER TABLE...DROP COLUMN is blocked when 
dropping a generated column. 
 
Example: 
create table t1 (c1 integer not null primary key,c2 char(9) not 
null,c3 char(8) not null generated always as (substr(c2,1,8))); 
 
alter table t1 drop column c3 
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command.  During SQL 
processing it returned: 
SQL0478N  DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object 
type "COLUMN" cannot be processed because there is an object 
"SQL091007173914120", of type "CHECK CONSTRAINT", which depends 
on it.  SQLSTATE=42893

**************************************************************** 
* USERS AFFECTED:                                              * 
* UNKNOWN                                                      * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* A database configuration parameter, AUTO_REVAL,              * 
* isintroducedin v9.7, and is set to DEFERRED by default for   * 
* new databasescreated in v9.7. When AUTO_REVAL is set to      * 
* anything that isnotDISABLED, ALTER TABLE...DROP COLUMN is    * 
* blocked whendropping a generated column.Example:create table * 
* t1 (c1 integer not null primary key,c2 char(9)notnull,c3     * 
* char(8) not null generated always as(substr(c2,1,8)));alter  * 
* table t1 drop column c3DB21034E  The command was processed   * 
* as an SQL statementbecauseit was not a valid Command Line    * 
* Processor command.  DuringSQLprocessing it returned:SQL0478N * 
*  DROP, ALTER, TRANSFER OWNERSHIP or REVOKE onobjecttype      * 
* "COLUMN" cannot be processed because there is an             * 
* object"SQL091007173914120", of type "CHECK CONSTRAINT",      * 
* whichdependson it.  SQLSTATE=42893                           * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Set AUTO_REVAL to DISABLED, the default behaviour is         * 
* CASCADEDROP.Upgrade to DB2 Version 9.7 Fix Pack 2.           * 
****************************************************************

Local Fix: Set AUTO_REVAL to DISABLED, the default behaviour is 
CASCADE DROP

DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 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 9a 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 10 for Linux, UNIX, and Windows
 

First fixed in DB2 Version 9.7 Fix Pack 2.

keiner bekannt / siehe Local-Fix

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

13.10.2009
29.06.2010
29.06.2010

9.7.FP2