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

Chapter 2. DB2 UDB’s materialized views 21

Materialized views were introduced in DB2 UDB Version 5, and have been

continuously enhanced with new capabilities since then.

2.2 Materialized view CREATE considerations

Figure 2-2 provides an overview of the steps involved in creating a materialized

view.

Figure 2-2 CREATE materialized view overview

Note: Prior to DB2 UDB V8, materialized views were named Automatic

Summary Tables (ASTs) in IBM product documentation. While it was possible

to define non-aggregate materialized views in DB2 UDB V7, the restriction

was that such a materialized view could only be defined on single base table.

In DB2 UDB V8, this restriction has been removed, and since materialized

views can include other than summary data, the more generalized term

Materialized Query Tables (MQT) was introduced. ASTs can be considered to

be a subset of the generalized MQT which specifically includes summary data.

Attention: Throughout this document, we use the more widely known term

materialized views to mean ASTs/MQTs.

Is the

materialized view

MAINTAINED BY

SYSTEM?

Tune the materialized view

(indexes, runstats)

CREATE the materialized view

(may be MAINTAINED BY SYSTEM or

MAINTAINED BY USER)

materialized view goes into

CHECK PENDING NO ACCESS state

REFRESH TABLE

statement

(resets

CHECK PENDING NO ACCESS

state)

NO

Reset

CHECK PENDING NO ACCESS

state using

SET INTEGRITY statement

YES

SET INTEGRITY

statement

(resets

CHECK PENDING NO ACCESS

state)

MAINTAINED BY USER

Use

LOAD or IMPORT

to populate

the materialized view

STEP 2

STEP 3

STEP 1

POPULATE THE MATERIALIZED VIEW