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

Chapter 2. DB2 UDB’s materialized views 27

Table 2-1 Refresh considerations

2.3.1 Deferred refresh

This maintenance approach is used when the materialized view need not be kept

in sync with the base tables as the base tables are being updated. The data may

be refreshed when appropriate as deemed by the administrator. Such

materialized views are called REFRESH DEFERRED tables.

Example 2-4 shows an example of SQL for creating a REFRESH DEFERRED

materialized view.

Example 2-4 Creating a refresh deferred materialized view

CREATE SUMMARY TABLE dba.summary_sales

AS (SELECT ...........)

DATA INITIALLY DEFERRED

REFRESH DEFERRED

Items REFRESH IMMEDIATE REFRESH DEFERRED

System maintained only System maintained User maintained only

Static SQL Optimization No optimization No optimization

Dynamic SQL Optimization Optimization Optimization

SQL INSERT,

UPDATE, DELETE

against

materialized view

Not permitted Not permitted Permitted

REFRESH TABLE

tablename

Permitted Permitted Not applicable

REFRESH TABLE

NOT

INCREMENTAL

Permitted Permitted Not applicable

REFRESH TABLE

INCREMENTAL

Permitted Requires staging table Not applicable

Staging table Not applicable Same restrictions to

creating them, as those

applying to REFRESH

IMMEDIATE materialized

views

Not applicable

28 High-Function Business Intelligence in e-business

Figure 2-3 provides an overview of maintaining a REFRESH DEFERRED

materialized view.

Figure 2-3 Deferred refresh

A REFRESH DEFERRED materialized view may be maintained via a REFRESH

TABLE command with either a full refresh (NOT INCREMENTAL) option, or an

incremental refresh (INCREMENTAL) option.

򐂰 With a full refresh, DB2 deletes the contents of the contents of the

materialized view, scans the base table(s), computes and generates all the

necessary rows, and then inserts these rows in to the materialized view.

򐂰 With an incremental refresh, a staging table

6

must be defined for the

materialized view. DB2 synchronously updates the staging table as the base

tables are being updated, and then computes the delta joins and aggregates

for updating the materialized view when the incremental refresh is requested.

Restriction: Materialized view optimization does not occur for static SQL

statements with REFRESH DEFERRED tables.

6

See “Incremental refresh” on page 29 for further details.

Important: Only MAINTAINED BY SYSTEM materialized views support the

REFRESH TABLE command.

Base Table

T2

Staging

Table

ST1

Base Table

Tn

Base Table

T1

Full Refresh

Incremental Refresh

MV

T1,T2,..Tn

synchronous

SQL

INSERTs

UPDATEs

DELETEs

+

LOAD

delta aggregate

Chapter 2. DB2 UDB’s materialized views 29

A brief discussion of the full refresh and incremental refresh follows:

Full refresh

The following statement will request a full refresh of the materialized view

dba.summary_sales:

REFRESH TABLE dba.summary_sales NOT INCREMENTAL

The NOT INCREMENTAL option specifies a full refresh for the materialized view

by recomputing the materialized view definition. When this is done, all existing

data within the table is deleted, and the query defining the materialized query

table is computed in it's entirety. For the duration of a full refresh, DB2 takes a

share lock on the base tables, and a z-lock on the materialized view. Depending

upon the size of the base tables, this process can take a long time. The base

tables are not updatable for this duration, and the materialized view is not

available for access or optimization. An additional consideration is that significant

logging may occur during a full refresh as it populates the materialized view.

Incremental refresh

The following statement will request an incremental refresh of the materialized

view dba.summary_sales:

REFRESH TABLE dba.sales_summary INCREMENTAL

The INCREMENTAL option specifies an incremental refresh for the materialized

view by considering only the consistent content of an associated staging table.

Note: If DB2 detects that the materialized view needs to be fully recomputed,

then an error condition is returned.

Important: If neither INCREMENTAL nor NOT INCREMENTAL is specified on

the REFRESH TABLE statement, the system will determine whether

incremental processing is possible. If not possible, full refresh will be used.

The following actions apply:

򐂰 If a staging table is present for the materialized view that is to be refreshed,

and incremental processing is not possible because the staging table is in

a pending state, an error is returned.

򐂰 Full refresh will be performed if the staging table is inconsistent and the

staging table is pruned.

򐂰 Incremental refresh will be performed using the contents of a valid staging

table, and the staging table will be pruned.