38 Patterns: Information Aggregation and Data Integration with DB2 Information Integrator
The DB2 query optimizer uses the information in the global catalog and the data
source wrapper to plan the optimal way to process SQL statements. Execution
plans for federated queries are chosen by the same DB2 optimizer that optimizes
regular queries. The difference is that the federated engine uses the native client
interface to each target data source, and sends queries to it in its own dialect.
Figure 2-6 summarizes some of the DB2 II components on a Windows platform.
Figure 2-6 DB2 Information Integrator on a Windows platform
2.4.3 Configuring the federated system
The DB2 federated server allows you to access and join data from relational and
non-relational data sources. By setting the database manager configuration
parameter FEDERATED to YES, the DB2 instance (without DB2 II) allows
federated access to other DB2 sources, Informix, and any OLE DB source, as
shown in Figure 2-6.
DB2 Information Integrator
DB2 8.0
Oracle 9i
Non Relational Wrappers
User Defined Functions
Table Structured
Lotus Extended
Search 4.0
Excel Wrapper
XML Wrapper
Table Structured Files Wrapper
Extended Search Wrapper
DB2 UDF for MQ
DB2 UDF for
IDMMX (Scoring)
DB2 XML Extender
WebSphere MQ Application
Message Interface
DB2 Intelligent
Miner Scoring 8.1
Relational Wrappers
DB2 Client
Oracle Client
DB2 Wrapper
Oracle Wrapper
ODBC Wrapper
Chapter 2. DB2 Information Integration architecture overview 39
Figure 2-7 on page 40 highlights the basic steps involved in configuring the
federated system. Some of these steps may be optional depending upon the
data source being configured. Most of the steps to configure access to a data
source can be accomplished through the DB2 Control Center. Use the DB2
Command Center for the steps that require a command line.
Attention: If you need access to other non-relational or non-IBM relational
sources such as Oracle, Sybase, or Microsoft SQL databases as well as
generic ODBC access, and Teradata, then you need to install DB2 II.
Attention: Before configuring access to a data source, ensure that the
federated server has been set up properly. It is especially important to:
򐂰 Link DB2 to the client software. This creates the data source wrapper
libraries on the federated server.
򐂰 Set up the data source environment variables.
For further details, refer to the IBM DB2 Information Integrator: Installation
Guide, GC18-7036.
40 Patterns: Information Aggregation and Data Integration with DB2 Information Integrator
Figure 2-7 Basic steps in configuring a federated system
Each of these steps is described briefly:
Step 1 involves preparing the federated server for the data source. For the
DB2 family, this involves cataloging the node and the remote database. For
Informix, Sybase, and Microsoft SQL Server data sources, it involves setting
up and testing the client configuration file.
Step 2 involves creating the wrappers in the federated server. One wrapper is
created for each type of data source to be accessed. When a wrapper is
created, it is registered in the federated database and the wrappers can now
be used to access objects from these data sources.
Step 3 involves creating the server definition that defines the data source to
be accessed by the federated database. The name of the data source and
other information is part of the server definition.
For a relational DBMS (RDBMS), it includes the type and version of the
RDBMS, the database name for the data source on the RDBMS, and
metadata that is specific to the RDBMS. A DB2 data source can have
multiple databases, and therefore a database name is required to identify
it as the target. An Oracle data source, on the other hand, can only have a
Step 7 - Test the nickname
Step 2 - Create the wrapper
Step 3 - Create the server definition
Step 6 - Create nickname
Function mapping
Data mapping
Step 1 - Prepare the federated server for the data source
Step 5 - Test connection to the data source server
Step 4 - Create the user mappping

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

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