|In the user-managed approach, it is left to the user to|
To use SQL INSERT statements, you must first take the MQT out of CHECK PENDING NO ACCESS state, if appropriate. However, you must first disable optimization before resetting the CHECK PENDING NO ACCESS state via the DISABLE QUERY OPTIMIZATION option in the DDL to ensure that a dynamic SQL query does not accidentally optimize to this MQT while the data in it is still in a state of flux. This warning applies to user-managed MQTs with the DEFERRED REFRESH option. Once the materialized view has been populated, the optimization needs to be enabled and the underlying tables need to be made read/write using the SET INTEGRITY command.
Note that at any time a system-maintained MQT can be put back in CHECK PENDING NO ACCESS state using the SET INTEGRITY FOR tablename OFF statement. The ‘REFRESH TABLE tablename NOT INCRE-MENTAL’ can be used if you want DB2 to fully refresh the MQT.
|DB2 Materialized Query Tables||221|
When an MQT is dropped, all dependencies are dropped and all packages with dependencies on the MQT are invalidated. Views based on dropped MQTs are marked inoperative.
The SQL for creating a REFRESH DEFERRED MQT could look like:
CREATE SUMMARY TABLE dba.summary_salesAS (SELECT ...........)