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

44 High-Function Business Intelligence in e-business

2.7.1 State considerations

The following state considerations apply for DB2 to even consider materialized

view optimization:

򐂰 Materialized view must be created with the ENABLE QUERY OPTIMIZATION

parameter.

򐂰 CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION register must

enable optimization of the particular table type. This register can be set to:

SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION = ALL|NONE|SYSTEM|USER

򐂰 For REFRESH DEFERRED materialized views, the CURRENT REFRESH

AGE register must be set to ANY.

SET CURRENT REFRESH AGE ANY|0

򐂰 REFRESH IMMEDIATE materialized views are always current and are always

candidates for materialized view optimization, regardless of the CURRENT

REFRESH AGE register setting.

򐂰 For dynamic and static SQL, the QUERY OPTIMIZATION level must be set to

2, or greater than equal to 5.

– Default value is 5, and this default can be changed in the

DFT_QUERYOPT parameter in the database configuration file

– The level can be changed as follows

SET CURRENT QUERY OPTIMIZATION LEVEL 2

򐂰 Materialized view cannot be in a CHECK PENDING NO ACCESS state.

2.7.2 Matching criteria considerations

Matching is the process of reviewing the user query, and evaluating the potential

use of a materialized view for query rewrite.

Assuming that the state criteria are not inhibitors, the query rewrite component

reviews the following criteria to determine the viability of using the materialized

view in the query rewrite.

We discuss these criteria as:

򐂰 Matching permitted

򐂰 Matching inhibited

Quite often, a materialized view may not exactly match the user query, and DB2

may have to incur some extra processing to massage the materialized view data

to deliver the desired result. This extra processing is called

compensation.

List

item 2 on page 46 shows an example of compensation.