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.
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
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.
- Open the First steps window by clicking Start --> Programs --> IBM DB2 --> Set-up Tools --> First Steps.
- Select Create Sample Database from the left navigation tree.
- Select DB2 UDB Sample on the first wizard page.
- 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;
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.
Copyright, 2003 International Business Machines Corporation. All rights reserved.
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.