![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Before you start
The scenario in this tutorial uses DB2 and Oracle relational database management systems. You should have general familiarity with relational databases and data modeling concepts, including:
Data model: A conceptual representation of data structures that are required by a database. The data structures include data objects, associations between data objects, and rules that define operations on such objects. The data model focuses on what data is required and how it should be organized, rather than what operations will be performed on the data. It is a plan for building a database.
Entity-relationship (ER) model: A conceptual data model. The model visually represents these concepts by the entity-relationship diagram. The basic constructs of the ER model are entities, relationships, and attributes. Entities are concepts (real or abstract) about which information is collected. Relationships are associations between the entities. Attributes are properties that describe the entities.
Database view: A read-only virtual table composed of the result set of a query. A view can subset the data contained in one or more tables without any storage overhead.
Federated server: A database manager instance that acts as a virtual layer that submits requests from user applications to a variety of data sources through a common, unified interface. This is the server instance that is installed by WebSphere Information Integrator.
Nickname: An identifier that is used by the federated server to reference an object located at the remote data source.
User mapping: Usually required to define an association between the federated server authorization ID and the data source user ID and password.
Server definition: An identifier used by the federated server to reference a remote database or node.
This tutorial shows you, step by step, how to:
- Create a new logical data model.
- Transform that logical data model to a physical data model.
- Discover and browse remote data sources using embedded capabilities of WebSphere Information Integrator.
- Reverse engineer remote databases to physical data models.
- Map the extracted physical models to the new logical data model.
- Generate a view that defines those remote databases in terms of the new model.
- Deploy the generated view to a database and use it to access integrated data across the two databases in terms of the new model.
After taking this tutorial, you should understand how Rational Data Architect (Data Architect) capabilities enhance the federation capabilities of the WebSphere Information Integrator. Step-by-step instructions are given to provide you with hands-on experience using this modeling tool.
Unzip the Data Architect files ar-wbirdacode.zip into a directory. You need to use the following files from the zip file in the order they are listed:
- db2dj.ini
- Contains the environment variable ORACLE_HOME. Modify this file to use
the path where the Oracle client software is installed on the federated server. For example,
ORACLE_HOME=C:\Oracle\Ora10g. Place the file in the %DB2PATH%\cfg directory. For example,
C:\Program Files\IBM\SQLLIB. Issue a
db2stopanddb2startfrom your DB2 command line. - tnsnames.ora
- Modify your Oracle client software's %ORACLE_HOME%\NETWORK\ADMIN\tnsnames.ora file on the federated server to contain a valid server with the name SANTA_CLARA_COUNTY, as shown in this file. This server will be used as the Santa Clara County's library system in our scenario.
- federation_setup.sql
- In this script you will create a federated server that will be used as a
virtual layer to connect to both the LIBRARY DB2 database and SANTA_CLARA_COUNTY Oracle database.
The script creates a federated server called FEDERATE, creates tables for the school library on the LIBRARY
database, creates tables for the county on the SANTA_CLARA_COUNTY database, and populates both sets
of tables with sample data.
Modify the script to use the user IDs and passwords for the LIBRARY and SANTA_CLARA_COUNTY databases. For example, if your user ID is
farnazwith a password oftab0uli, replace DB2 user ID in the script withfarnazand DB2 password withtab0uli. Execute this script by issuingdb2 -tvf federation_setup.sql
from the DB2 command line on the federated server.
- new library system.ldm
- You will import this logical data model in Creating a new logical data model.
- newlibraryfinal.msl
- You will import this mapping model in Discovering relationships.
- location_and_number_of_c++_books.sql
- You will use this script in Deployment.
- books_by_negeen_sahari.sql
- You will use this script in Deployment.
This tutorial uses the general announced version of Rational Data Architect V6.1, WebSphere Information Integrator V8.2, and Oracle 10g and is written from the perspective of a new installation of Data Architect, WebSphere Information Integrator, and Oracle software. However, any of the following relational data sources are supported by Data Architect and can be used instead: DB2 Universal Database (UDB), DB2 UDB iSeries, DB2 UDB zSeries, Oracle, Sybase, Informix, and Microsoft® SQL Server.
Table of contents
- Before you start
- Overview
- Our scenario
- Creating a new data design project
- Creating a new logical data model
- Transforming to a physical data model
- Connecting to a federated database
- Discovering remote databases
- Reverse engineering physical data from a database
- Mapping existing schemas to the new library system
- Discovering relationships
- Generating union views
- Deployment
- Summary
- Download
- Resources
- About the authors
Local resources
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular tutorial tags |
My tutorial tagsSkip to tags list
Popular tutorial tags |
My tutorial tags
