Enabling a materialized query table for automatic query rewrite

After you populate a user-maintained materialized query table, you can alter the table to enable query optimization.

Before you begin

If the materialized query table is user-maintained, it is populated with data.

About this task

Begin program-specific programming interface information.When creating a user-maintained materialized query table, initially disable query optimization. Otherwise, Db2 might automatically rewrite queries to use the empty materialized query table.

Procedure

To enable a materialized query table for automatic query rewrite:

Issue an ALTER TABLE statement and specify:
  • The ENABLE QUERY OPTIMIZATION clause
  • An isolation level equivalent or higher than that of the dynamic queries that might use the materialized query table. The isolation level of the table must be equal to or higher than the isolation level of the dynamic query being considered for automatic query rewrite.
End program-specific programming interface information.