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.