Populating a new materialized query table

You can populate a newly created table for the first time by using the REFRESH TABLE statement or by using INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements, or the LOAD utility.

About this task

When you create a materialized query table with the CREATE TABLE statement, the table is not immediately populated.

Procedure

To populate a materialized query table:

  • Issue a REFRESH TABLE statement.
    Begin general-use programming interface information.For example, the following REFRESH TABLE statement populates a materialized query table named SALESCNT:
    REFRESH TABLE SALESCNT;
    End general-use programming interface information.

    You should avoid using the REFRESH TABLE statement to update user-maintained materialize query tables because the REFRESH TABLE statement uses a fullselect and can result in a long-running query.

    The REFRESH TABLE statement is an explainable statement. The explain output contains rows for INSERT with the fullselect in the materialized query table definition.

  • Use the INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements, or the LOAD utility.

    You cannot use the INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements, or the LOAD utility to change system-maintained materialized query tables.