|
Problem IC81328
|
Status: Geschlossen |
ALTER TABLE FAILS WITH SQL0270N DUE TO NON-EXISTENT MQT DEPENDENCY |
| Produkt: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problembeschreibung: |
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-Zusammenfassung: |
****************************************************************
* 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. |
| verfügbare FixPacks: |
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
|
| Lösung |
Fixed in v9.7 Fix pack 7 |
| Workaround |
keiner bekannt / siehe Local-Fix |
| Weitere Daten |
Datum - Problem gemeldet :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
| 09.02.2012
02.04.2013
28.06.2013 |
| Problem behoben ab folgender Versionen (IBM BugInfos) |
9.7.FP7
|
| Problem behoben lt. FixList in der Version |
| 9.7.0.7
|
 |
