Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

Chapter 2. DB2 Information Integration architecture overview 45

The CREATE FUNCTION MAPPING statement gives considerable control

over the scope of the mapping. For example, you can:

• Create a function mapping for all data sources of a specific type such

as all Informix data sources.

• Create a function mapping for all data sources of a specific type and

version, such as all Oracle 9 data sources.

• Create a function mapping for all data source objects located on a

specific server.

• Disable a default function mapping. Default function mappings can not

be dropped.

For further details on function mappings, refer to the IBM DB2 Information

Integrator Data Source Configuration Guide Version 8, available as

softcopy from the Web site:

http://www.ibm.com/software/data/integration/solution

For further details on column functions, data mapping, and function mapping,

refer to IBM DB2 Information Integrator Federated Systems Guide,

SC18-7364.

7. Step 7 involves checking to ensure that the nickname has been configured

correctly by issuing an SQL statement against it as follows:

SELECT count(*) FROM nickname

2.4.4 Performance considerations

Probably the most significant concern about federated technology is the issue of

acceptable performance. IBM invests heavily in query optimization research and

development.

The DB2 Information Integrator optimizer takes into account standard statistics

from source data (such as cardinality or indexes), data server capability (such as

join features or built-in functions), data server capacity, I/O capacity, and network

Note: This query may not be appropriate for some data sources, for

example, Lotus Extended Search.

Attention: Appendix B, “Configuring data sources in DB2 Information

Integrator” on page 165, provides examples of configuring a number of the

data sources used in the CFS portal described in Chapter 4, “The Druid

Financial Corporation (DFC) Customer Insight solution” on page 115.

46 Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

speed. The following capabilities of the DB2 optimizer have a significant impact

on the quality of the access plan generated:

򐂰

Query rewrite logic rewrites queries for more efficient processing. For

example, it can convert a join of unions that drives a tremendous amount of

data traffic, into a union of joins that leverages query power at the data server

and thereby minimizes data traffic back to the federated server. The database

administrator (DBA) can define materialized query tables (MQTs), which the

DB2 optimizer can transparently leverage via query rewrite to satisfy user

queries.

򐂰

Pushdown analysis (PDA) capability identifies which operations can be

executed at the data server prior to returning results to the federated server.

The DB2 optimizer can perform a nested loop join that queries a small table

on one server, and uses the results as query predicates to a large table on

another.

This section describes performance factors influencing federated queries as

follows:

򐂰 Performance factors

򐂰 Pushdown concept

򐂰 Federated server options for best performance

򐂰 Nickname column options for best performance

򐂰 Indexes and statistics

Performance factors

Factors that influence federated query performance include:

1. The processing power of local and remote machines, as well as the

bandwidth of the intervening communication network.

2. Quality of the generated query execution plans at the federated server and

the remote sources. The query execution plans influence the number of

interactions required between the federated server and the remote sources,

and the amount of data that is moved.

The amount of data moved mainly depends upon two factors:

a. The amount of processing and filtering that can be pushed down (see

“Pushdown concept” on page 47) to the remote data sources

If there are some filtering predicates in the WHERE-clause, and the

remote source is able to apply those predicates, then the federated server

Data movement between the federated server and the remote source is a

key performance factor.