Altering materialized query table properties

With some restrictions, you can change a materialized query table to a regular table or a regular table to a materialized query table. You cannot change other table types; only regular and materialized query tables can be changed. For example, you cannot change a replicated materialized query table to a regular table, nor the reverse.

About this task

Once a regular table has been altered to a materialized query table, the table is placed in a set integrity pending state. When altering in this way, the fullselect in the materialized query table definition must match the original table definition, that is:

The number of columns must be the same.
The column names and positions must match.
The data types must be identical.

If the materialized query table is defined on an original table, then the original table cannot itself be altered into a materialized query table. If the original table has triggers, check constraints, referential constraints, or a defined unique index, then it cannot be altered into a materialized query table. If altering the table properties to define a materialized query table, you are not allowed to alter the table in any other way in the same ALTER TABLE statement.

When altering a regular table into a materialized query table, the fullselect of the materialized query table definition cannot reference the original table directly or indirectly through views, aliases, or materialized query tables.

To change a materialized query table to a regular table, use the following command:

   ALTER TABLE sumtable
      SET SUMMARY AS DEFINITION ONLY

To change a regular table to a materialized query table, use the following command:


    ALTER TABLE regtable
      SET SUMMARY AS <fullselect>

The restrictions on the fullselect when altering the regular table to a materialized query table are very much like the restrictions when creating a summary table using the CREATE SUMMARY TABLE statement.