DB2 FOR LUW / DB2FORLUW / 970 - DB2

User receives SQL0270N when attempting to ALTER or DROP TABLE: 
 
ALTER TABLE mytbl ALTER COLUMN c1 NOT NULL 
 
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command.  During SQL 
processing it returned: 
SQL0270N  Function not supported (Reason code = "21"). 
SQLSTATE=42997 
 
Reason code = "21" is defined as follows: 
 
A column cannot be dropped, or have its length, data type, 
security, or nullability altered on a table which is a base 
table for a materialized query table. 
 
Drop table fails with the following error: 
 
DROP TABLE mytbl 
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command.  During SQL 
processing it returned: 
SQL0901N  The SQL statement failed because of a non-severe 
system error. Subsequent SQL statements can be processed. 
(Reason "Dependent MST's packed Descriptor not found".) 
SQLSTATE=58004 
 
What both errors are suggesting is that the table MYTBL has a 
materialized query table (MQT) defined against it. Still, this 
is not the case. All MQTs referencing the table were previously 
dropped. Indeed, db2look shows no MQTs defined against the said 
table (as expected). 
 
However, packed descriptor (PD) dump for MYTBL is still showing 
an MQT dependency: 
 
          Dependent MST DESCRIPTION 
          ------------------------- 
 
 Schema of the dependent Table     : *** 
 Table name of the dependent Table : ***.*** 
 Refresh Option                    : Deferred 
 MST type                          : Regular 
 Isolation Level                   : 1 
 Arithmetic Warning Level          : 78 
 ... 
 
[contact DB2 Support for assistance with dumping and formatting 
table PD] 
 
The problem is that the table packed descriptor was not properly 
updated following the drop of MQT.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to v9.7 Fix pack 7                                    * 
****************************************************************

Contact DB2 Support for assistance with regenerating the table 
packed descriptor. This process will remove the leftover MQT 
dependency. 
 
Note: Once the table packed descriptor is regenerated by db2cat 
tool, you should be able to access the table again. But this is 
only a temporary fix. You would have to re-create the affected 
table by EXPORT, DROP and CREATE.

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
 

Fixed in v9.7 Fix pack 7

keiner bekannt / siehe Local-Fix

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

09.02.2012
02.04.2013
28.06.2013

9.7.FP7