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:
For further details on column functions, data mapping, and function mapping,
refer to IBM DB2 Information Integrator Federated Systems Guide,
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
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
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
This section describes performance factors influencing federated queries as
򐂰 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.

Get Patterns: Information Aggregation and Data Integration with DB2 Information Integrator now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.