The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

All information submitted is secure.

My developerWorks:

My notifications:

Sign out

Select a language:

Configuring IBM DB2 Information Integrator to Access Diverse Data

Larissa Wojciechowski (larissaw at us.ibm.com.), Staff Software Engineer, IBM

Larissa N. Wojciechowski is a staff software engineer in the Data Management User Technology group at IBM Silicon Valley Lab in San Jose, California. Since she joined IBM in 2000, she has worked on the user interface and online assistance for DB2 Relational Connect, DB2 Life Sciences Data Connect, DB2 Spatial Extender, and DB2 Information Integrator. If you have any comments or suggestions for this tutorial, please send her an email at larissaw at us.ibm.com.

Summary: With IBM DB2 Information Integrator, you can access and join structured and unstructured data from several data sources by using a single SQL statement. In this tutorial, you will learn how to set up access to the data sources from your DB2 federated database using the DB2 Control Center.

Date: 02 Jul 2003
Level: Introductory
PDF: A4 and Letter (61 KB | 17 pages)Get Adobe® Reader®

Activity: 2302 views
Comments:

Getting started

Overview

With IBM DB2 Information Integrator, you can access and join structured and unstructured data from several data sources by using a single SQL statement. In this tutorial, you will learn how to set up access to the data sources from your DB2 federated database using the DB2 Control Center. In the following modules you will configure access to DB2 UDB, XML, and Microsoft Excel data sources.

This tutorial consists of 4 modules. Each module will take 10 to 20 minutes to complete.

To configure access to these data sources, you must use DB2 Information Integrator Version 8.1. You should be familiar with the DB2 Control Center, DB2 Command Center, and DB2 Command Line Processor.

Before you begin

You must install and configure DB2 Information Integrator before you begin this tutorial.

Download and unzip the XML and Excel tutorial files (3 KB) in a directory on your local drive. In this tutorial, the directory is referred to as your <x:\tutorial> directory.

Then, set up a federated database. To set up a federated database:

Create a database to use as the DB2 federated database. This database is referred to as <tutorial>. In this tutorial, the federated database is named IDUG. You will create your federated database objects for the data sources in this database. From the DB2 Command Line Processor enter:
create db <tutorial>
where <tutorial> is the name of the database that you are creating.
Update the database configuration parameters for <tutorial> to enable the federated functionality. From the DB2 Command Line Processor enter:
update dbm config using federated YES

Next, you will need to set up a DB2 database as your remote data source. To configure DB2 as a remote data source:

Create a sample database from the DB2 First Steps window. You will access the database with the DB2 federated database.
1. Open the First steps window by clicking Start --> Programs --> IBM DB2 --> Set-up Tools --> First Steps.
2. Select Create Sample Database from the left navigation tree.
3. Select DB2 UDB Sample on the first wizard page.
4. Follow the steps in the Create Sample Database wizard to create the database.

In the SAMPLE database, create a table called CREDITLIMIT by entering the following commands from the DB2 Command Line Processor:

connect to sample;
create table creditlimit ( name varchar(48), limit integer);
insert into creditlimit values ('Bob Arenas', 100);
insert into creditlimit values ('Danny Dampier', 1000);
insert into creditlimit values ('Dan Clevera', 200);
insert into creditlimit values ('Dean Sura', 150);
insert into creditlimit values ('Lisa Lubekey', 50);
insert into creditlimit values ('Oliver Murphy', 300);
select * from creditlimit;

connect reset;

Tutorial scenario

You are a database administrator for an online store that is facing several integration challenges. The customer and product data is stored in several sources and in several different formats. The sales team takes orders and stores customer orders and information in XML files. The operations team stores item numbers and their prices in Microsoft Excel spreadsheets. The accounting department uses DB2 tables to store information about the customers and their credit limits. An application developer must query all of this data to gather information about current customers, the types of items they purchase, how much each customer spends, and which customers have spent beyond their limit.

The application developer asks you to set up a system so that he can query this information from several sources. You install IBM DB2 Information Integrator to help integrate the data so that you can retrieve and administer the data easily.

Here are the data sources and the information that they provide:

XML files: items and the names of customers who purchased the items
Microsoft Excel spreadsheets: price
DB2 UDB table: credit limit

Using the DB2 Control Center, you create access to the federated data sources and run queries to test the connections. To access these data sources, you must create wrappers, servers, user mappings, and nicknames. After each data source is configured, you use the DB2 Command Center to query data from the different data sources.

Notices and Trademarks

IBM, DB2, DB2 Universal Database, and DB2 Information Integrator are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.

Microsoft is a registered trademark of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Other company, product, and service names may be trademarks or service marks of others.

Comments