Refreshing dependent immediate materialized query tables

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;