Chapter 2. DB2 UDBs materialized views 41
materialized view optimization as well. Since this is the final materialized view
on SALES, the NO DATA MOVEMENT state is reset on SALES.
The above features significantly improve the availability of the materialized views,
and thereby the scalability and performance of queries.
2.5 Materialized view ALTER considerations
THE ALTER statement can be used to convert a materialized view to a regular
table, and vice versa.
The following statement converts an existing materialized view into a regular
table. This causes all the packages dependent on this materialized view to be
The following statement converts an ordinary table into an materialized view,
where the summary-table-definition defines the query and
ALTER TABLE tablename SET SUMMARY AS summary-table-definition
ALTER may be used for several reasons:
򐂰 Correcting the materialized view options to address changing requirements
over time.
򐂰 Temporarily taking materialized view optimization offline for maintenance,
such as creating indexes.
򐂰 Taking it offline to avoid logging overhead as described in , Deferred refresh
considerations on page 33.
The following restrictions apply to changing a regular table into an materialized
view. The regular table must not:
򐂰 Already be a materialized view
򐂰 Be a typed table
Important: If the FULL ACCESS option is chosen on the SET INTEGRITY
SALES step, then the NO DATA MOVEMENT state is
not set on the SALES
table. What this means is that full read/write access is permitted on SALES,
and therefore incremental update is no longer possible on the SALES_SUM
and SALES_SUM_REGION tables. When a REFRESH TABLE is issued
against these tables, a full refresh is done which has a negative impact on
availability of the materialized view. The decision to use FULL ACCESS is
therefore an implementation choice.

Get DB2 UDB's High-Function Business Intelligence in e-business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.