Changing the attributes of a materialized query table

You can use the ALTER TABLE statement to change the attributes of an existing materialized query table.

Procedure

Begin general-use programming interface information.To change the attributes of an existing materialized query table:

  1. Issue the ALTER TABLE statement.
  2. Decide which attributes to alter.
    Option Description
    Enable or disable automatic query rewrite. By default, when you create or register a materialized query table, Db2 enables it for automatic query rewrite. To disable automatic query rewrite, issue the following statement: 
    ALTER TABLE TRANSCOUNT DISABLE QUERY 
OPTIMIZATION;
    Switch between system-maintained and user-maintained. By default, a materialized query table is system-maintained; the only way you can change the data is by using the REFRESH TABLE statement. To change to a user-maintained materialized query table, issue the following statement:
    ALTER TABLE TRANSCOUNT SET MAINTAINED 
BY USER;
    Change back to a system-maintained materialized query table. Specify the MAINTAINED BY SYSTEM option.
    End general-use programming interface information.