Creating tables for staging data

A staging table allows incremental maintenance support for deferred materialized query table. The staging table collects changes that must be applied to the materialized query table to synchronize it with the contents of underlying tables. The use of staging tables eliminates the high lock contention caused by immediate maintenance content when an immediate refresh of the materialized query table is requested. Also, the materialized query tables no longer must be entirely regenerated whenever a REFRESH TABLE is performed.

About this task

Materialized query tables are a powerful way to improve response time for complex queries, especially queries that might require some of the following operations:
  • Aggregated data over one or more dimensions
  • Joins and aggregates data over a group of tables
  • Data from a commonly accessed subset of data
  • Repartitioned data from a table, or part of a table, in a partitioned database environment
Here are some of the key restrictions regarding staging tables:
  1. The query used to define the materialized query table, for which the staging table is created, must be incrementally maintainable; that is, it must adhere to the same rules as a materialized query table with an immediate refresh option.
  2. Only a deferred refresh can have a supporting staging table. The query also defines the materialized query table associated with the staging table. The materialized query table must be defined with REFRESH DEFERRED.
  3. When refreshing using the staging tables, only a refresh to the current point in time is supported.
  4. Partitioned hierarchy tables and partitioned typed tables are not supported. (Partitioned tables are tables where data is partitioned into multiple storage objects based on the specifications provided in the PARTITION BY clause of the CREATE TABLE statement.)
An inconsistent, incomplete, or pending state staging table cannot be used to incrementally refresh the associated materialized query table unless some other operations occur. These operations will make the content of the staging table consistent with its associated materialized query table and its underlying tables, and to bring the staging table out of pending. Following a refresh of a materialized query table, the content of its staging table is cleared and the staging table is set to a normal state. A staging table might also be pruned intentionally by using the SET INTEGRITY statement with the appropriate options. Pruning will change the staging table to an inconsistent state. For example, the following statement forces the pruning of a staging table called STAGTAB1:
   SET INTEGRITY FOR STAGTAB1 PRUNE;

When a staging table is created, it is put in a pending state and has an indicator that shows that the table is inconsistent or incomplete with regard to the content of underlying tables and the associated materialized query table. The staging table needs to be brought out of the pending and inconsistent state in order to start collecting the changes from its underlying tables. While in a pending state, any attempts to make modifications to any of the staging table's underlying tables will fail, as will any attempts to refresh the associated materialized query table.

There are several ways a staging table might be brought out of a pending state; for example:
  • SET INTEGRITY FOR <staging table name> STAGING IMMEDIATE UNCHECKED
  • SET INTEGRITY FOR <staging table name> IMMEDIATE CHECKED