DB2 UDB's High-Function Business Intelligence in e-business

Chapter 2. DB2 UDB’s 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

invalidated.

ALTER TABLE tablename SET SUMMARY AS DEFINITION ONLY

The following statement converts an ordinary table into an materialized view,

where the summary-table-definition defines the query and

refreshable-table-options.

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.