Chapter 2. DB2 UDB’s materialized views 27
Table 2-1 Refresh considerations
2.3.1 Deferred refresh
This maintenance approach is used when the materialized view need not be kept
in sync with the base tables as the base tables are being updated. The data may
be refreshed when appropriate as deemed by the administrator. Such
materialized views are called REFRESH DEFERRED tables.
Example 2-4 shows an example of SQL for creating a REFRESH DEFERRED
materialized view.
Example 2-4 Creating a refresh deferred materialized view
CREATE SUMMARY TABLE dba.summary_sales
AS (SELECT ...........)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
Items REFRESH IMMEDIATE REFRESH DEFERRED
System maintained only System maintained User maintained only
Static SQL Optimization No optimization No optimization
Dynamic SQL Optimization Optimization Optimization
SQL INSERT,
UPDATE, DELETE
against
materialized view
Not permitted Not permitted Permitted
REFRESH TABLE
tablename
Permitted Permitted Not applicable
REFRESH TABLE
NOT
INCREMENTAL
Permitted Permitted Not applicable
REFRESH TABLE
INCREMENTAL
Permitted Requires staging table Not applicable
Staging table Not applicable Same restrictions to
creating them, as those
applying to REFRESH
IMMEDIATE materialized
views
Not applicable
28 High-Function Business Intelligence in e-business
Figure 2-3 provides an overview of maintaining a REFRESH DEFERRED
materialized view.
Figure 2-3 Deferred refresh
A REFRESH DEFERRED materialized view may be maintained via a REFRESH
TABLE command with either a full refresh (NOT INCREMENTAL) option, or an
incremental refresh (INCREMENTAL) option.
? With a full refresh, DB2 deletes the contents of the contents of the
materialized view, scans the base table(s), computes and generates all the
necessary rows, and then inserts these rows in to the materialized view.
? With an incremental refresh, a staging table
6
must be defined for the
materialized view. DB2 synchronously updates the staging table as the base
tables are being updated, and then computes the delta joins and aggregates
for updating the materialized view when the incremental refresh is requested.
Restriction: Materialized view optimization does not occur for static SQL
statements with REFRESH DEFERRED tables.
6
See “Incremental refresh” on page 29 for further details.
Important: Only MAINTAINED BY SYSTEM materialized views support the
REFRESH TABLE command.
Base Table
T2
Staging
Table
ST1
Base Table
Tn
Base Table
T1
Full Refresh
Incremental Refresh
MV
T1,T2,..Tn
synchronous
SQL
INSERTs
UPDATEs
DELETEs
+
LOAD
delta aggregate
Chapter 2. DB2 UDB’s materialized views 29
A brief discussion of the full refresh and incremental refresh follows:
Full refresh
The following statement will request a full refresh of the materialized view
dba.summary_sales:
REFRESH TABLE dba.summary_sales NOT INCREMENTAL
The NOT INCREMENTAL option specifies a full refresh for the materialized view
by recomputing the materialized view definition. When this is done, all existing
data within the table is deleted, and the query defining the materialized query
table is computed in it's entirety. For the duration of a full refresh, DB2 takes a
share lock on the base tables, and a z-lock on the materialized view. Depending
upon the size of the base tables, this process can take a long time. The base
tables are not updatable for this duration, and the materialized view is not
available for access or optimization. An additional consideration is that significant
logging may occur during a full refresh as it populates the materialized view.
Incremental refresh
The following statement will request an incremental refresh of the materialized
view dba.summary_sales:
REFRESH TABLE dba.sales_summary INCREMENTAL
The INCREMENTAL option specifies an incremental refresh for the materialized
view by considering only the consistent content of an associated staging table.
Note: If DB2 detects that the materialized view needs to be fully recomputed,
then an error condition is returned.
Important: If neither INCREMENTAL nor NOT INCREMENTAL is specified on
the REFRESH TABLE statement, the system will determine whether
incremental processing is possible. If not possible, full refresh will be used.
The following actions apply:
? If a staging table is present for the materialized view that is to be refreshed,
and incremental processing is not possible because the staging table is in
a pending state, an error is returned.
? Full refresh will be performed if the staging table is inconsistent and the
staging table is pruned.
? Incremental refresh will be performed using the contents of a valid staging
table, and the staging table will be pruned.
30 High-Function Business Intelligence in e-business
Figure 2-4 provides an overview of the steps involved in creating, enabling and
exploiting a staging table on the materialized view shown in Example 2-5.
Example 2-5 Materialized view with REFRESH DEFERRED option
CREATE SUMMARY TABLE summary_sales
AS (SELECT ...........)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
Figure 2-4 Incremental refresh with staging table
We discuss each of these steps briefly.
Step 1
The staging table is created on the previously defined materialized view in this
step.
Restriction: For this to be successful, even though the
sales_summary
table
is defined with the REFRESH DEFERRED option, it must satisfy all the
conditions of a REFRESH IMMEDIATE materialized view as described in
“Materialized view limitations” on page 92.
Note: Both the materialized view sales_summary and the staging table
sales_stage are in a CHECK PENDING NO ACCESS state.
CREATE TABLE sales_stage
FOR sales_summary
PROPAGATE IMMEDIATE
SET INTEGRITY FOR sales_stage
ALL IMMEDIATE UNCHECKED
REFRESH TABLE sales_summary
NOT INCREMENTAL
CREATE STAGING
TABLE
MAKE STAGING
TABLE AVAILABLE
IN IT IA L PO PULATIO N
OR FULL REFRESH
REFRESH TABLE sales_summary
INCREMENTAL
INCREMENTAL
REFRESH
UPD ATE / DELETE / IN SERT & LO AD INSERT
activity
Step 2
Step 3
Step 5
Step 1
Step 4
Chapter 2. DB2 UDB’s materialized views 31
The PROPAGATE IMMEDIATE parameter in the CREATE TABLE statement
indicates that any changes made to the base tables as part of an INSERT,
DELETE, UPDATE operation are immediately added to the staging table, with
additional information generated in the three extra columns of the staging
table.This is done as part of the same SQL statement.
The schema of the staging table looks much like the materialized view for which it
has been defined. The difference is that the staging table may have two or three
more columns than its associated materialized view. These additional columns
are as follows:
? GLOBALTRANSID CHAR(8) — global transaction ID for each propagated row
? GLOBALTRANSTIME CHAR(13) — the timestamp of the transaction
? OPERATIONTYPE INT — values -1, 0 and 1 for SQL DELETE, UPDATE and
INSERT respectively
For replicated
7
materialized views and non-aggregate query materialized views,
the staging table contains three more columns than the associated materialized
view. Otherwise, the staging table only contains two extra columns, with the
OPERATIONTYPE column being omitted.
Step 2
Issuing the SET INTEGRITY statement against the staging table takes it out of
the CHECK PENDING NO ACCESS state, thus making it available for
processing.
Step 3
Issuing the REFRESH TABLE statement against the materialized view
sales_summary with the NOT INCREMENTAL option, populates the materialized
view and takes it out of CHECK PENDING NO ACCESS state on successful
completion.
Step 4
This step reflects ongoing update activity against the underlying base tables. For
SQL operations, the staging table is updated synchronously within the same unit
of work as the SQL INSERT, UPDATE or DELETE statement.
Restriction: Each column name in the staging table must be unique and
unqualified. If a list of column names is not specified, the columns of the table
inherit the names of the columns of the associated summary table and the
additional columns are defined. If a list of columns is specified, it has to
include the required extra columns.
7
This applies to a partitioned database environment.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.