Chapter 2. DB2 UDB’s materialized views 37
zero latency between the base tables and materialized view before choosing
the REFRESH IMMEDIATE option.
? Another consideration is that lock contention may be an issue for higher level
aggregation rows. The higher the level of aggregation, the greater the chance
of concurrency issues against the materialized view when multiple users are
concurrently performing updates on the base tables. For example, a
materialized view containing the total sales for the year may need to update
the row for the current active year for every sales transaction that pertains to
the current year.
One way to reduce contention on such REFRESH IMMEDIATE materialized
views, is to redefine it as a REFRESH DEFERRED materialized view, and
associate a staging table with it. An incremental refresh of this materialized
view will significantly reduce this lock contention. However, since the
semantics of data latency changes with this option, you should carefully
evaluate the business requirements of zero latency between the base tables
and materialized view before choosing this approach to reduce lock
contention.
2.4 Loading base tables (LOAD utility)
The base tables of a materialized view may be updated either through SQL
statements, or via the LOAD utility.
In DB2 V7, when a LOAD was performed on the base tables,
all the
corresponding materialized views were put in a CHECK PENDING NO ACCESS
state, until complete synchronization of the materialized view and base tables
had been accomplished via the SET INTEGRITY or REFRESH TABLE
statements. When the tables involved are very large, the time to refresh may be
very large, since the entire base data is scanned and not just the recently
appended data. This can result in very poor response times for user queries,
since materialized view optimization would be inhibited, because of its CHECK
PENDING NO ACCESS state (see “Matching criteria considerations” on page 44
for details of limitations of matching).
Attention: The following discussion applies to REFRESH IMMEDIATE
materialized views and staging tables.
Important: Remember that LOAD INSERT appends rows after the last page
of the table.
38 High-Function Business Intelligence in e-business
In DB2 V8, functionality has been added to improve the availability of
materialized views during a load of base tables as follows:
1. Reduce the time it takes to refresh a materialized view by only scanning the
LOAD appended data. This applies to both refresh deferred and refresh
immediate materialized views.
2. Keep the materialized views available for optimization during and after the
load, by blocking access to the appended data, until all the (refresh
immediate
9
) materialized views have been synchronized with the base tables.
In order to support this high availability functionality, the following capabilities
have been added:
? Three new table states have been introduced:
– CHECK PENDING READ ACCESS state:
This allows read access to tables, but only up to and
not including the first
loaded page.
– CHECK PENDING NO ACCESS state:
This was previously just called the CHECK PENDING state, and had to be
renamed given the new CHECK PENDING READ ACCESS state.
– NO DATA MOVEMENT state:
This ensures that the RID of a row cannot change. Therefore operations
such as REORG, or REDISTRIBUTE, or update of a partitioning key, or an
update of a key in an multi-dimensional cluster (MDC) table will all be
inhibited. SQL insert, update (excepting those mentioned) and delete
operations do not change the RIDs, and are therefore permitted.
? New options have been added to the LOAD:
– CHECK PENDING CASCADE DEFERRED | IMMEDIATE:
CASCADE DEFERRED specifies that descendent foreign key tables, and
descendent refresh immediate and staging tables are
not put into CHECK
PENDING NO ACCESS state, but left in normal state.
– ALLOW READ ACCESS | NO ACCESS:
ALLOW READ ACCESS specifies that all the data prior to the first page
appended can continue to be read, but not updated.
? A new option has been added to SET INTEGRITY:
– FULL ACCESS:
9
By definition, refresh deferred materialized views do not care about data latency issues.
Chapter 2. DB2 UDB’s materialized views 39
This option specifies that full read write access should be allowed on the
table, even if there are dependent materialized views for this table that
have not yet been refreshed with the newly load appended data. If this
option is not specified, the table has the NO DATA MOVEMENT mode set
on it.
Assume a scenario shown in Figure 2-6. Here SALES is the base table on which
two materialized views SALES_SUM and SALES_SUM_REGION are defined.
The SALES table has check constraints in its definition (such as region code
checking), and is also involved in referential integrity constraints with the
PRODUCT and STORE_SALES tables.
Figure 2-6 LOAD application sample
Assume a LOAD insert
10
is done on SALES with the CHECK PENDING
CASCADE DEFERRED option.
LOAD INSERT INTO SALES ..... CHECK PENDING CASCADE DEFERRED...ALLOW READ
ACCESS...
1. LOAD issues a SET INTEGRITY SALES OFF which causes SALES to be put
in CHECK PENDING READ ACCESS state (because of the ALLOW READ
ACCESS option), and the data gets loaded.
2. STORE_SALES, PRODUCT, SALES_SUM and SALES_SUM_REGION are
left in normal state because of the CHECK PENDING CASCADE
DEFERRED option.
10
This causes data to be appended to existing data in the table. If LOAD replace is done, the entire
contents of the table are deleted and replaced by the new data. The CHECK PENDING CASCADE
DEFERRED option can still be used for LOAD REPLACE, but the ALLOW READ ACCESS option
cannot be used for LOAD REPLACE.
SALES_SUM_REGION
STORE_SALES
PRODUCT
SALES SALES_SUM
40 High-Function Business Intelligence in e-business
SQL statements will only be able to access the SALES table data prior to the
beginning of the first loaded page
(because of the ALLOW READ ACCESS
option), and also be able to use SALES_SUM and SALES_SUM_REGION for
optimization because they are still in sync. This has expanded the window of
availability of the materialized view and base table.
3. At the end of the load, SALES still has the CHECK PENDING READ
ACCESS state set.
4. Next
11
a SET INTEGRITY SALES ....IMMEDIATE CHECKED....is issued for
verifying the integrity of the new data loaded. This takes an exclusive lock on
SALES and puts it into a NO DATA MOVEMENT state, and also puts the
SALES_SUM and SALES_SUM_REGION materialized views in a CHECK
PENDING NO ACCESS state. STORE_SALES will remain in normal state
since the rows added do not affect the referential integrity relationship with
STORE_SALES. The PRODUCT table is also unaffected by the rows added
to SALES and is therefore left in normal state.
At this point, materialized view optimization will be suspended because both
the SALES_SUM and SALES_SUM_REGION materialized views are in
CHECK PENDING NO ACCESS state.
5. Assuming a successful SET INTEGRITY SALES step, we issue a REFRESH
TABLE SALES_SUM statement which results in an incremental update using
only the data after the first loaded page, which is a much faster operation than
scanning the entire base table. This reduces the window of availability of the
SALES_SUM materialized view as well. When this refresh is completed
successfully, the CHECK PENDING NO ACCESS state is reset on
SALES_SUM, but not on SALES_SUM _REGION which has not been
refreshed as yet. SALES continues to be in the NO DATA MOVEMENT state.
6. Now a REFRESH TABLE SALES_SUM_REGION causes that table to be
taken out of CHECK PENDING NO ACCESS state and available for
11
The SET INTEGRITY step is not required if the base table has no parent tables, descendent
tables, check constraints, or generated columns.
Note: SET INTEGRITY SALES will cause local check constraints to be
verified, as well as referential integrity violations checked against the
PRODUCT table. These checks may fail, which would result in states being
rolled back to the way it was at the end of the load.
Note: SALES_SUM materialized view is now available for optimization,
while SALES_SUM_REGION is not, since it is in CHECK PENDING NO
ACCESS state.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.