DB2 UDB's High-Function Business Intelligence in e-business

26 High-Function Business Intelligence in e-business

3. IMPORT or LOAD the data into the materialized view — these operations are

permitted on a table in CHECK PENDING NO ACCESS state.

4. Reset the CHECK PENDING NO ACCESS state using the following

statement:

SET INTEGRITY FOR tablename ALL IMMEDIATE UNCHECKED

This action is recorded in the CONST_CHECKED column

5

(value ‘U’) in the

catalog table SYSCAT.TABLES column, indicating that the user has assumed

responsibility for data integrity of the materialized view.

5. Make the base tables read/write.

The process described in Example 2-3 can also be used to populate a

maintained by user materialized view.

2.2.3 Step 3: Tune the materialized view

This involves the creation of appropriate indexes, and executing the RUNSTATS

utility on the materialized view to ensure optimal access path selection.

“Materialized view tuning considerations” on page 87 describes these

considerations in greater detail.

2.3 Materialized view maintenance considerations

DB2 supports two approaches for maintaining materialized views — a deferred

refresh approach, and an immediate refresh approach. These approaches are

described in this section.

Table 2-1 summarizes some considerations relating to refresh immediate and

refresh deferred materialized views.

Important: SQL INSERT statements cannot be issued against a table in

CHECK PENDING NO ACCESS state. If the user wishes to populate the

materialized view using SQL INSERT statements, then (s)he must first reset

the CHECK PENDING NO ACCESS state. However, optimization must first be

disabled via the DISABLE QUERY OPTIMIZATION option in the CREATE

DDL to ensure that a dynamic SQL query does not accidentally optimize to

this materialized view while the data in it is still in a state of flux. Once the

materialized view has been populated, then optimization needs to be enabled.

5

CONST_CHECKED is defined as a CHAR(32) and is viewed as an array, where the value stored at

CONST_CHECKED(5) represents a summary table.