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).
24 High-Function Business Intelligence in e-business
Users may want to avoid this logging overhead during the initial population of
the materialized view by following these steps:
1. Make the base tables read only.
2. Extract the required data from the base tables, and write it to an external file.
3. IMPORT or LOAD the extracted 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 on the materialized view
using the following statement:
SET INTEGRITY FOR tablename ALL IMMEDIATE UNCHECKED
5. Make the base tables read/write.
Another option, which overcomes the concern of extracting data correctly to an
external file, is to use the LOAD the data from a cursor, as shown in
Example 2-3.
Example 2-3 LOADing from a cursor
-- base table definition
CREATE TABLE t1
(
c1 INT,
c2 INT,
c3 INT
);
-- create the refresh deferred materialized view
CREATE TABLE a1 AS
(
SELECT c1, count(*) AS cs
Note: There is no semantic difference between using the SET INTEGRITY or
the REFRESH TABLE syntax; both are treated identically.
Attention: Using the SET INTEGRITY or REFRESH TABLE approaches to
populate the materialized view involves using SQL INSERT subselect type
processing, which may result in excessive logging when very large
materialized views are being populated.
Important: With this approach, it is the user’s responsibility to ensure that the
data being loaded into the materialized view correctly matches the query
definition of the materialized view.
Chapter 2. DB2 UDB’s materialized views 25
FROM t1
GROUP BY c1
)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
-- make the base table read only
-- ensure that the cursor is not closed automatically
UPDATE COMMAND OPTIONS USING C OFF;
-- cursor declaration that mirrors materialized view query definition
DECLARE cc CURSOR FOR
SELECT c1, COUNT(*)
FROM t1
GROUP BY c1;
-- LOAD the materialized view with the contents of the cursor
LOAD FROM CC OF CURSOR REPLACE INTO a1;
-- reset the CHECK PENDING NO ACCESS state onthe materialized view
SET INTEGRITY FOR a1 ALL IMMEDIATE UNCHECKED;
-- make the base table read/wite
When incremental refresh
4
is supported for such materialized views, REFRESH
TABLE can subsequently be used to perform the necessary ongoing
maintenance of this materialized view when the underlying tables are updated.
Populating a MAINTAINED BY USER materialized view
In the user-managed approach, it is the user’s responsibility to populate the
materialized view, and make it available for matching by resetting the CHECK
PENDING NO ACCESS state. The user is responsible for ensuring the
consistency and integrity of the materialized view. Typically, the user would:
1. Make the base tables read only.
2. Extract the required data from the base tables, and write it to an external file.
4
See 2.3.1, “Deferred refresh” on page 27 for a discussion of incremental refresh.
Note: When incremental refresh is not supported for such materialized views,
it may be more appropriate to create materialized views as MAINTAINED BY
USER, and adopt the following approach for populating user maintained
materialized views.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.