|
Problem IC81328
|
Status: Closed |
ALTER TABLE FAILS WITH SQL0270N DUE TO NON-EXISTENT MQT DEPENDENCY |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Update to v9.7 Fix pack 7 *
**************************************************************** |
| Local Fix: |
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. |
| available fix packs: |
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 |
Fixed in v9.7 Fix pack 7 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 09.02.2012
02.04.2013
28.06.2013 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP7
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.7
|
 |
