Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

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

for

Z/OS

DB2

8.1

Oracle 9i

Lotus

Notes

Non Relational Wrappers

User Defined Functions

Excel

XML

Table Structured

Files

Lotus Extended

Search 4.0

Excel Wrapper

XML Wrapper

Table Structured Files Wrapper

Extended Search Wrapper

Scoring

Database

WebSphere

MQ

Web

Services

DB2 UDF for XML

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

ODBC

DB2 Wrapper

Oracle Wrapper

ODBC Wrapper

Windows

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:

1.

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.

2.

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.

3.

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