Propagating dependent immediate staging tables

If the table being loaded is an underlying table of a staging table with the immediate propagate attribute, and if the load operation is done in insert mode, the subsequent propagation into the dependent immediate staging tables is incremental.

During incremental propagation, the rows corresponding to the appended rows in the underlying tables are appended into the staging tables. Incremental propagation is faster in the case of large underlying tables with small amounts of appended data. Performance is also improved if the staging table is used to refresh its dependent deferred materialized query table. There are cases in which incremental propagation is not allowed, and the staging table is marked incomplete. That is, the staging byte of the CONST_CHECKED column has a value of F. In this state, the staging table can not be used to refresh its dependent deferred materialized query table, and a full refresh is required in the materialized query table maintenance process.

If a table is in incomplete state and the INCREMENTAL option has been specified, but incremental propagation of the table is not possible, an error is returned. If any of the following have taken place, the system turns off immediate data propagation and sets the table state to incomplete:
  • A load replace operation has taken place on an underlying table of the staging table, or the NOT LOGGED INITIALLY WITH EMPTY TABLE option has been activated after the last integrity check on the underlying table.
  • The dependent materialized query table of the staging table, or the staging table has been loaded in REPLACE or INSERT mode.
  • An underlying table has been taken out of Set Integrity Pending state before the staging table has been propagated by using the FULL ACCESS option during integrity checking.
  • An underlying table of the staging table has been checked for integrity non-incrementally.
  • The table space containing the staging table or its underlying table has been rolled forward to a point in time, and the staging table and its underlying table reside in different table spaces.

If the staging table has a W value in the CONST_CHECKED column of the SYSCAT.TABLES catalog, and the NOT INCREMENTAL option is not specified, incremental propagation to the staging table takes place and the CONST_CHECKED column of SYSCAT.TABLES is marked as 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 UT1 of staging table G1 and its dependent deferred materialized query table AST1. 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,G1 IMMEDIATE CHECKED;          
					
   REFRESH TABLE AST1 INCREMENTAL;