If the underlying table of an immediate refresh materialized
query table is loaded using the INSERT option, executing
the SET INTEGRITY statement on the dependent materialized query tables
defined with REFRESH IMMEDIATE results in an incremental
refresh of the materialized query table.
During an incremental refresh, the rows corresponding to the appended
rows in the underlying tables are updated and inserted into the materialized
query tables. Incremental refresh is faster in the case of large underlying
tables with small amounts of appended data. There are cases in which
incremental refresh is not allowed, and full refresh (that is, recomputation
of the materialized query table definition query) is used.
When the
INCREMENTAL option is specified, but
incremental processing of the materialized query table is not possible,
an error is returned if:
- A load replace operation has taken place into an underlying table
of the materialized query table or the NOT LOGGED INITIALLY WITH EMPTY
TABLE option has been activated since the last integrity check on
the underlying table.
- The materialized query table has been loaded (in either REPLACE
or INSERT mode).
- An underlying table has been taken out of Set Integrity Pending
state before the materialized query table is refreshed by using the FULL
ACCESS option during integrity checking.
- An underlying table of the materialized query table has been checked
for integrity non-incrementally.
- The materialized query table was in Set Integrity Pending state
before an upgrade.
- The table space containing the materialized query table or its
underlying table has been rolled forward to a point in time, and
the materialized query table and its underlying table reside in different
table spaces.
If the materialized query table has one or more W values
in the CONST_CHECKED column of the SYSCAT.TABLES catalog, and if
the NOT INCREMENTAL option is not specified in the
SET INTEGRITY statement, the table is incrementally refreshed and
the CONST_CHECKED column of SYSCAT.TABLES is marked U to
indicate that not all data has been verified by the system.
The following example illustrates a load insert operation into
the underlying table UTI of the materialized query table AST1. UT1
is checked for data integrity and is placed in the no data movement
mode. UT1 is put back into full access state once the incremental
refresh of AST1 is complete. In this scenario, both the integrity
checking for UT1 and the refreshing of AST1 are processed incrementally.
LOAD FROM IMTFILE1.IXF of IXF INSERT INTO UT1;
LOAD FROM IMTFILE2.IXF of IXF INSERT INTO UT1;
SET INTEGRITY FOR UT1 IMMEDIATE CHECKED;
REFRESH TABLE AST1;