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

22 High-Function Business Intelligence in e-business

We now briefly describe these steps:

2.2.1 Step 1: Create the materialized view

Assuming that the user has determined what the materialized view should look

like (see 2.8, “Materialized view design considerations” on page 60 for a

discussion of the design considerations), the following occurs when the

materialized view creation DDL is executed.

򐂰 Since the materialized view has not yet been populated, it is placed in

CHECK PENDING NO ACCESS

2

state regardless of whether it is a system

maintained or a user maintained materialized view. No SQL read or write

access is permitted against tables in a CHECK PENDING NO ACCESS state.

򐂰 Dependencies regarding the base tables and the materialized view are

recorded in SYSCAT.TABLES, SYSCAT.TABDEP, SYSCAT.VIEWS just as any

other table or view definition creation.

򐂰 All packages that update the base tables on which the materialized view is

built are invalidated if the REFRESH IMMEDIATE option is chosen, or it is a

REFRESH DEFERRED materialized view that is associated with a staging

table. This is because the SQL compiler must add appropriate operations in

the package to support the refresh immediate materialized views or staging

tables. When the package is first accessed after invalidation, an automatic

rebind ensures that the package has been updated to support the

materialized view or staging table.

An EXPLAIN of the rebound package will highlight the additional SQL

operations being performed to support materialized views.

2.2.2 Step 2: Populate the materialized view

DB2 supports materialized views that are either:

򐂰

MAINTAINED BY SYSTEM (default): In this case, DB2 ensures that the

materialized views are updated, when the base tables on which they are

created get updated. Such materialized views may be defined as either

REFRESH IMMEDIATE, or REFRESH DEFERRED. If the REFRESH

DEFERRED option is chosen, then either the INCREMENTAL or NON

INCREMENTAL refresh option can be chosen during refresh. This is

discussed in detail in 2.3, “Materialized view maintenance considerations” on

page 26.

򐂰

MAINTAINED BY USER: In this case, it is up to the user to maintain the

materialized view whenever changes occur to the base tables. Such

materialized views

must be defined with the REFRESH DEFERRED option.

2

This was previously called the CHECK PENDING state.

Chapter 2. DB2 UDB’s materialized views 23

Even though the REFRESH DEFERRED option is required, unlike

MAINTAINED BY SYSTEM, the INCREMENTAL or NON INCREMENTAL

option does

not apply to such materialized views, since DB2 does not

maintain such materialized views.

A couple of possible scenarios where such materialized views could be

defined are as follows:

a. For efficiency reasons, when the user is convinced that (s)he can

implement materialized view maintenance far more efficiently than the

mechanisms used by DB2. For example, the user has high performance

tools for rapid extraction of data from base tables, and loading the

extracted data into the materialized view.

b. For leveraging existing “user maintained” summaries, where the user

wants DB2 to automatically consider them for optimization for new ad hoc

queries being executed against the base tables.

Appendix C, “Materialized view syntax elements” on page 241 provides details

about the syntax, etc.

Populating a MAINTAINED BY SYSTEM materialized view

Typically, one of the following two approaches can be used to populate a

MAINTAINED BY SYSTEM materialized view. These are described briefly as

follows:

1. SET INTEGRITY

The following statement causes the materialized view to be populated, and

results in the CHECK PENDING NO ACCESS state being reset on successful

completion.

SET INTEGRITY FOR tablename IMMEDIATE CHECKED

3

2. REFRESH TABLE

The following statement also causes the materialized view to be populated,

and the CHECK PENDING NO ACCESS state to be reset on successful

completion.

REFRESH TABLE tablename

3

Since we do not have constraints on materialized views, we should not specify exception tables for

materialized views.

Note: SET INTEGRITY statement also applies to staging tables (see 2.3.1,

“Deferred refresh” on page 27 for details on staging tables).