IBM Support

Why do you lose an MQT if one of its base tables gets dropped?

Technote (FAQ)


If you drop one or more tables involved in a query associated with a view, the definition of that view will be left unchanged in the system catalog. You'd expect the same approach for a materialized query table (MQT) too, but DB2 rather invalidates it after dropping any of the base tables. Why do you lose an MQT if any of its base tables gets dropped?


There is an architectural difference between the views and MQTs in how they react on dropping their underlying objects. This note explains this difference and helps to choose appropriate approach when designing the databases and database applications.


An MQT and a view are different entities. An MQT is a base table with materialized content although it share some properties of a view (the results of both MQTs and views are based on some queries). For a view, there is no object to drop as this entity is the result of a query associated with it.

Instead, a primary use of MQTs is automatic query routing - queries against the base tables can be automatically routed to the MQTs. When an underlying object of an MQT is dropped, the content of the MQT must be made inaccessible. Even after the underlying object is re-created, the old content of the MQT must not be used and must be re-computed before the MQT can be used again. If not, query routing to the MQT may see incorrect results. This is an issue of data integrity.

Related information

Dropping tables

Document information

More support for: DB2 for Linux, UNIX and Windows
Database Objects/Config - Tables

Software version: 9.1, 9.5, 9.7, 9.8

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1626795

Modified date: 28 March 2013

Translate this page: