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

66 High-Function Business Intelligence in e-business

2.8.5 Step 5: Verify query routes to “empty” the materialized view

Having satisfied ourselves that the size of the materialized view is not an issue,

we need to determine that the query will route to this materialized view. Since the

routing is determined by matching criteria and materialized view access cost

considerations, we need to eliminate the cost aspect of this routing

consideration.

The following steps will verify whether the query can route to the materialized

view based on matching criteria alone:

1. Create the materialized view

2. Remove it from the CHECK PENDING NO ACCESS state via the following

command:

SET INTEGRITY FOR tablename ALL IMMEDIATE UNCHECKED

3. Run runstats on the empty materialized view

4. Run EXPLAIN on the query, and verify that the query is being rewritten by the

DB2 optimizer to route to the materialized view.

If the user query is being routed to the materialized view, then we can be

confident that matching criteria are being met. However, we cannot be certain

that routing will occur when the materialized view is populated with data from the

base tables, since DB2 will weigh cost issues of materialized view access versus

base table access in order to come up with an optimal access plan. Cost issues

will be evaluated in Step 7: Introduce cost issues into materialized view routing.

2.8.6 Step 6: Consolidate materialized views

Once all the queries have been processed, we need to try and minimize the

number of materialized views. One approach is to review those materialized

views that only have a few user queries routing to them, and try and consolidate

them into a few number.

Attention: If EXPLAIN indicates that the user query is not being routed to the

materialized view, then the problem is with the matching criteria. The user

query and/or the materialized view definition must be modified based on

matching criteria discussed in “Matching criteria considerations” on page 44,

and re-EXPLAINed to determine successful routing to the materialized view.

Note: Such consolidation efforts should keep size considerations in mind,

while ensuring that all affected user queries continue to be routed to the

appropriate consolidated materialized views.