Refreshing user-maintained materialized query tables

You can update the data in user-maintained materialized query tables by using the INSERT, UPDATE, MERGE, TRUNCATE, and DELETE statements, and the LOAD utility.

About this task

Begin program-specific 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 to refresh a materialized query table, the statement can result in a long-running query. Use insert, update, delete, or load operations might be more efficient than using the REFRESH TABLE statement.

Depending on the size and frequency of changes in base tables, you might use different strategies to refresh your materialized query tables. For example, for infrequent, minor changes to dimension tables, you could immediately propagate the changes to the materialized query tables by using triggers. For larger or more frequent changes, you might consider refreshing your user-maintained materialized query tables incrementally to improve performance.

Procedure

To avoid refresh a user-maintained materialized query table:

Use INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements, or the LOAD utility.
For example, you might find it faster to generate the data for your materialized query table and execute the LOAD utility to populate the data.

Example

For example, assume that you need to add a large amount of data to a fact table. Then, you need to refresh your materialized query table to reflect the new data in the fact table. To do this, perform these steps:

  • Collect and stage the new data in a separate table.
  • Evaluate the new data and apply it to the materialized table as necessary.
  • Merge the new data into the fact table

For an example of such code, see member DSNTEJ3M in DSN1210.SDSNSAMP, which is shipped with Db2.End program-specific programming interface information.