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

Chapter 2. DB2 UDB’s materialized views 19

Example 2-1 and Example 2-2 show examples of creating a materialized view.

Details of the syntax are described in Appendix C-1, “Main syntax elements of

materialized views” on page 242.

Example 2-1 Example of creating a deferred refresh materialized view

CREATE TABLE custtrans AS

(

SELECT cust_id, COUNT(*) AS counttrans

FROM trans

WHERE cust_id > 500

GROUP BY cust_id

)

DATA INTITIALLY DEFERRED REFRESH DEFERRED

Example 2-2 Example of creating a refresh immediate materialized view

CREATE TABLE dba.trans_agg AS

(

SELECT ti.pgid, t.locid, t.acctid, t.status,

YEAR(pdate) as year, MONTH(pdate) AS month,

SUM(ti.amount) AS amount, COUNT(*) AS count

FROM transitem AS ti, trans AS t

WHERE ti.transid = t.transid

GROUP BY YEAR(pdate), MONTH(pdate)

)

DATA INITIALLY DEFERRED REFRESH IMMEDIATE

A number of other parameters such as DISABLE | ENABLE QUERY

OPTIMIZATION, MAINTAINED BY SYSTEM | USER, PROPAGATE IMMEDIATE

etc. are applicable to materialized views, and are allowed to default in the

examples shown.

2.1.3 Materialized view usage considerations

The decision to implement materialized views will depend upon answers to the

following questions:

1. Is it acceptable for the user application if the user query gets different results

depending on whether the query routes to the materialized view, or accesses

the base tables directly?

Note: In both these examples, it is the “DATA INITIALLY DEFERRED

REFRESH DEFERRED | IMMEDIATE” that identifies it as a materialized view.

The SUMMARY keyword that was part of the earlier syntax for creating

materialized views is not required, but is supported for backward compatibility.