Altering an existing materialized query table

You can use the ALTER TABLE statement to change the attributes of a materialized query table or change a materialized query table to a base table.

About this task

Altering a materialized query table to enable it for query optimization makes the table immediately eligible for use in automatic query rewrite. You must ensure that the data in the materialized query table is current. Otherwise, automatic query rewrite might return results that are not current.

In addition to using the ALTER TABLE statement, you can change a materialized query table by dropping the table and recreating the materialized query table with a different definition.

Procedure

To change the attributes of a materialized query table:

  1. Issue an ALTER TABLE statement.
    • Enable or disable automatic query rewrite for a materialized query table with the ENABLE QUERY OPTIMIZATION or DISABLE QUERY OPTIMIZATION clause.
    • Change the type of materialized query table between system-maintained and user-maintained by using the MAINTAINED BY SYSTEM or MAINTAINED BY USER clause.
  2. Change a materialized query table into a base table.
    Begin general-use programming interface information.For example Assume that you no longer want the TRANSCOUNT table to be a materialized query table. The following ALTER TABLE statement, which specifies the DROP MATERIALIZED QUERY clause, changes the materialized query table into a base table.
    ALTER TABLE TRANSCOUNT DROP MATERIALIZED QUERY;
    End general-use programming interface information.
    The column definitions and the data in the table do not change. However, Db2 can no longer use the table in automatic query rewrite. You can no longer update the table with the REFRESH TABLE statement.