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

Chapter 2. DB2 UDB’s materialized views 95

Certain operations cannot be performed on the base tables of a materialized

view that needs to be incrementally maintained.

򐂰 IMPORT REPLACE cannot be used on an base table of a materialized view.

򐂰 ALTER TABLE NOT LOGGED INITIALLY WITH EMPTY TABLE cannot be

done on a base table of a materialized view.

򐂰 Materialized views cannot be used as exception tables to collect information

when constraints are being validated during bulk constraints checking (during

LOAD or executing the SET INTEGRITY statement).

2.12 Replicated tables in nodegroups

In a partitioned database, the performance of join queries can be greatly

enhanced through collocation of rows of the different tables involved in the join.

Figure 2-16 describes such an environment, where the STORE and TRANS

tables have been partitioned on

storeid column. An SQL query that requires a

join on the

storeid column will see significant performance benefits from this

partitioning scheme, because of the greater parallelism achieved through

collocated joins.

However, when the CUST table is also involved in the join, then a collocated join

is not possible, since the CUST table does not have a

storeid column, and

therefore cannot be partitioned by

storeid. While DB2 UDB can choose to

perform a directed join in this particular case

14

, the performance of such joins is

less efficient than that of a collocated join, since the movement of rows is inline

with query execution.

Note: DB2 sometimes allows you to define a REFRESH DEFERRED table

even though it cannot use it for materialized view optimization. In such cases,

it issues a warning SQL20059W (sqlstate 01633).

A typical scenario for this functionality is when a database administrator needs

to:

1. Create a data mart based on detailed data from operational systems.

2. Provide direct access to end users to this data mart only,

without allowing

them access to the base detailed data.

3. Control the refresh cycle of this data mart via the REFRESH TABLE

statement.

This is an example of creating an materialized view that is directly accessible

by end users, and is not involved in materialized view optimization.

96 High-Function Business Intelligence in e-business

Figure 2-16 Collocation in partitioned database environment

Example 2-51 shows an example of creating such a materialized view, which

creates an intra-database replica.

Example 2-51 Creating a replicated table in a nodegroup

CREATE TABLE custr1 AS

(SELECT * FROM cust)

DATA INITIALLY DEFERRED REFRESH IMMEDIATE REPLICATED IN <a nodegroup>

14

Broadcast or repartitioned joins can be used when the tables are not joined on the partitioning

column.

Attention: We can now use materialized views to replicate tables to other

nodes to enable collocated joins to occur even though the all the tables are not

joined on the partitioned key. In Figure 2-16, CUST is replicated to the other

nodes using the materialized view infrastructure in order to enable collocated

joins for superior performance.

Attention: This form of replication should not be confused with the replication

provided by DB2 DataPropagator, which is typically used for

inter-database

replication, that is, for replicating data between different databases. Table 2-2

summarizes the differences between materialized view intra-database

replication, and DB2 DataPropagator inter-database replication.

TRANS

storeid2

TRANS

storeid1

TRANS

storeid3

STORE

storeid1

STORE

storeid2

STORE

storeid3

CUST

CUST

CUST

CUST

Replicated Tables

Good for colocated joins and avoiding

the movement of data between

partitions for each query.

Ideal for a star schema configuration

where dimension tables are not huge

and disk space is not an issue.

Chapter 2. DB2 UDB’s materialized views 97

Table 2-2 Intra-database replication versus inter-database replication

Replication

type

Definition Sources Targets Maintenance Usage

Intra-database

(materialized

view)

CREATE

TA B LE

statement

Local DB2 tables

only

Local DB2 tables

only

Synchronously

maintained

Existence of

materialized

view is

transparent to

the application

Inter-database

(DB2 Data

Propagator)

Source and

subscriptio

ns defined

through the

DB2

Control

Center, or

the

DataJoiner

DJRA tool

Local or remote

DB2 tables and

views.

Heterogeneous

relational sources

via DataJoiner

nicknames.

External data is

staged in a

consistent change

data format.

Local or remote

DB2 table.

Heterogeneous

relational targets

supported via

DataJoiner

nicknames, with

other external

targets via

consistent change

data table

interface.

On demand

using

subscription

events and/or

COPYONCE

Apply startup

option.

----------------------

Asynchronously

updated from

captured

changes —

either

timer-driven or

event-driven.

The

application

must explicitly

reference the

replica.

98 High-Function Business Intelligence in e-business