34 High-Function Business Intelligence in e-business
2.3.2 Immediate refresh
This maintenance approach is used when the materialized view must be kept in
sync with any changes in the base tables on which it has been defined are
updated. Such materialized views are called REFRESH IMMEDIATE tables.
Example 2-6 shows an example of SQL for creating a refresh immediate
materialized view.
Example 2-6 Creating a refresh immediate materialized view
CREATE SUMMARY TABLE dba.summary_sales
AS (SELECT ...........)
DATA INITIALLY DEFERRED
REFRESH IMMEDIATE
Tip: Incremental refresh should be used to reduce the duration of the refresh
process, and should be considered when one or more of the following
conditions exist:
? The volume of updates to the base tables relative to size of the base tables
is small.
? Duration of read only access to the base tables during a full refresh is
unacceptable.
? Duration of unavailability of the materialized view during a full refresh is
unacceptable.
Important: Not all materialized views can be defined to be REFRESH
IMMEDIATE. The principle behind what materialized view can be defined as
REFRESH IMMEDIATE is governed by the ability to compute the changes to
the materialized view from the delta changes to the base tables, and any other
base tables involved. Refer to 2.11, “Materialized view limitations” on page 92
for details about these restrictions.
Attention: Materialized view optimization occurs for both static and dynamic
SQL statements with REFRESH IMMEDIATE tables.
Note: The REFRESH TABLE statement can be issued against a REFRESH
IMMEDIATE materialized view — it is generally used for initially populating the
materialized view.
Chapter 2. DB2 UDB’s materialized views 35
REFRESH IMMEDIATE tables are synchronized with the base tables in the same
unit of work as the changes (inserts, updates or deletes) to the base tables.
Given the synchronous nature of the immediate refresh capability, the atomic
requirement for the change propagation can have a negative impact on
transactions updating the base tables.
An incremental update mechanism is used to synchronize a REFRESH
IMMEDIATE materialized view whenever update activity occurs against a base
table. The process involved is shown in Figure 2-5.
Figure 2-5 Immediate refresh using incremental update
When an SQL statement modifies a row in the base table, the following steps
occur in atomic fashion:
1. The modified row is captured.
2. The query defining the materialized view is computed based on the modified
row, computing the delta joins and delta aggregation to generate the data
necessary to update the materialized query table.
3. The delta is applied to the materialized view.
Note: This processing occurs at statement execution time as opposed to
occurring at commit time.
Base Table
T2
MV
T1,T2,..Tn
Base Table
Tn
Base Table
T1
Delta Select/Join
Delta
Aggregate
Delta Apply
SQL
INSERTs
UPDATEs
DELETEs
+
LOAD
Delta Propagation
in
same unit-of-work
36 High-Function Business Intelligence in e-business
Consider, for example, that you have an materialized view that has data grouped
by month. Assume that the data was up to and including the month of June. If a
sales entry is made for the month of June, the delta change to the aggregation is
computed so that the row in the materialized query table is updated to reflect the
newly inserted row in the base sales table. If a row is inserted into the sales table
for the month of July, a new row would be inserted in the materialized view since
one did not exist before for July.
When a LOAD INSERT operation is performed on the base table(s) of a
materialized view, the newly appended data is invisible for read access, thus
ensuring that the base table(s) and corresponding materialized views are still in
sync.
In order to make the appended data visible and synchronized with the
materialized view, perform the following steps:
1. Issue the following statement against the base table:
SET INTEGRITY FOR base_table_name IMMEDIATE CHECKED
This statement will make the base table available after verifying the
consistency of the newly appended rows to the base table. It will also cause a
CHECK PENDING NO ACCESS state to be set on the materialized view.
2. Issue a SET INTEGRITY or REFRESH TABLE of the materialized view with
the INCREMENTAL option to bring the materialized view in sync with all the
rows added by the LOAD INSERT operation.
See “Loading base tables (LOAD utility)” on page 37 for some of the options
available when loading data into a base table.
Immediate refresh considerations
Two main considerations apply here as follows:
? REFRESH IMMEDIATE materialized views require updates to the underlying
base table(s) to be reflected within the same unit of work. This atomic
requirement can have a significant negative impact on transactions updating
the base table(s). You should carefully evaluate the business requirements of
Note: DB2 may use pipelining or temporary tables to effect this operation.
Attention: Since this entire operation is atomic, any error encountered while
updating either the base table or the materialized view will roll back all the
changes during the unit of work. This guarantees the synchronization of the
materialized view with the base tables.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.