A Simple Introduction to Using DB2 Information Integrator with Oracle 9i

Document options Document options requiring JavaScript are not displayed My developerWorks needs you! Connect to your technical community Rate this page Help us improve this content

Level: Intermediate

Sridhar VarakalaIBM Software Services for WebSphere
Kulvir Bhogal, Consultant, IBM

19 Jun 2003

If you are interested in information integration but haven't had the time to pick up the basics, author Kulvir Bhogal offers a gentle but quick introduction to the capabilities of DB2 Information Integrator for integrating and updating data in an Oracle database.

The heterogeneous nature of today's enterprises

Let's face it, many enterprise operations are quite heterogeneous in nature. Many times, such heterogeneity is brought about by company mergers, or sometimes it can be boiled down to politics that change like the wind. In the case of the former, company mergers frequently try to combine disparate IT shops, hoping that their disparate setups will somehow magically work together. Reality check: such disparity can cause major headaches for IT shops trying to support these mergers.

Fortunately, you can use the federated server capability of the IBM® DB2® Information Integrator^TM to provide a way to create a single abstract view of diverse backend information sources. Let's say you have different backends consisting of Oracle, Microsoft® SQL Server, Sybase, and IBM DB2 Universal Database^TM. You may even have critical content in Documentum or in Lotus® Notes databases. The power of DB2 Information Integrator is quite extensive, allowing you to retain your current information stores while combining and consolidating the data from those sources in new ways Multiple, mixed-vendor, heterogeneous databases are connected together in such a way to provide a single application interface.

Using the federated server capability of DB2 Information Integrator, we can set up IBM DB2 so that we can integrate data in real time from different backends, update that data, and have the update reflected in the backend.

Back to top

What we'll be doing

In this article, we'll run you through a very simple business scenario so that you can witness first hand some of the powerful capabilities of DB2 Information Integrator. To keep the scenario simple, we'll be focusing on the relational capabilities only and will limit ourselves to two DBMS backend systems. In particular, we'll be tying together data in an Oracle 9i Database (version 9.2) and an IBM DB2 UDB 8.1 database using IBM DB2 Information Integrator Version 8.1.

Back to top

Our business scenario

A company named Big Auto Rental Inc. has just bought out a company named Little Car Rental Co. As part of the business takeover, Big Auto Rental Inc. agreed that Little Car Rental's IT infrastructure would be kept in place. However, for things to work properly in the business, a customer renting from Little Car Rental Co. must appear in the Big Auto Rental Inc. system.

Big Auto Rental Co. uses IBM DB2 8.1 UDB Enterprise Edition for their data storage while Little Car Rental Co. uses Oracle 9i Standard Edition Version 9.2. In our setup, we ran DB2 8.1, DB2 Information Integrator and Oracle 9i on the same physical box. Accordingly, you may have to modify our approach to emulate a distributed (more real world) environment.

We'll go about showing how the car rental companies' data can be "tied together" using IBM DB2 Information Integrator.

Back to top

Preparing Oracle - emulating Little Car Rental Co.'s setup

Little Car Rental Co. has a simple setup consisting of only one table residing in an Oracle 9i database. The table structure is shown below:

Let's use the SQL Plus utility of Oracle to create a user with the privileges to create and populate the table described above. After connecting to Oracle using an account that has privileges to create another user we issue the command:

 
SQL> CREATE USER oracleuser IDENTIFIED BY oraclepass; 
 
SQL> GRANT RESOURCE TO oracleuser; 

By issuing the command above, we effectively gave our new user, oracleuser, the following system privileges:

CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE

If you are unfamiliar with Oracle's syntax, you might want to check out the site http://sqlzoo.net/ which should get you up to speed with the syntax we use throughout this article.

Next, we grant our oracleuser the permission to connect to the database we created during our Oracle installation named oracle:

 
SQL> GRANT CREATE SESSION TO oracleuser; 

Next, we connect to our database that we created during the Oracle installation (again, in our case, which we named oracle):

 
SQL> CONN oracleuser/oraclepass@ORACLE.THINKER 

In the syntax above, ORACLE.THINKER is the name of our Net Service Name that we specified during installation.

You can confirm your Net Service name by taking a look at the file called tnsnames.ora, which is located in your c:\oracle\ora92\network\admin directory by default. A screenshot of the entry of particular interest where you can see where to get your Net Service Name is shown below in Figure 1.

Next we create our table:

 
SQL> CREATE TABLE LITTLECARRENTTABLE( 
		RENTALNUMBER DECIMAL(6,0) NOT NULL PRIMARY KEY, 
		RENTALCHARGES DECIMAL(6,2) NOT NULL, 
		CUSTOMERNAME CHARACTER (30) NOT NULL); 

And we populate our table with some sample records:

 
SQL> INSERT INTO LITTLECARRENTTABLE VALUES(1008,154.90,'Kulvir Bhogal'); 
SQL> INSERT INTO LITTLECARRENTTABLE VALUES(1012,68.00,'Sridhar Varakala'); 
SQL> INSERT INTO LITTLECARRENTTABLE VALUES(1015,360.00,'Barbara Lewis'); 

Back to top

Preparing DB2 - emulating Big Auto Rental Inc.'s setup

Now we'll put on our DB2 UDB hat for a while to emulate Big Auto Rental Inc.'s setup. The data model for their setup is show below:

As you can see, Big Auto's database setup is rather simple also. We'll use the DB2 Command Line Processor to create our table and populate it with some sample data:

 
db2 => CREATE DB BGAUTODB 
 
db2 => CONNECT TO BGAUTODB USER DB2ADMIN USING db2admin 
 
db2 => CREATE TABLE BIGCARRENTTABLE(CAR_ID INTEGER NOT NULL 
PRIMARY KEY, ACCOUNT_BALANCE DECIMAL (6,2) NOT NULL, CUSTOMER 
VARCHAR(30) NOT NULL) 
 
db2 => INSERT INTO BIGCARRENTTABLE VALUES(4567,234.50,'Richard Goldstein') 
 
db2 => INSERT INTO BIGCARRENTTABLE VALUES(7867,670.50,'James W. Carey') 
 
db2 => INSERT INTO BIGCARRENTTABLE VALUES(9044,342.17,'David Mark') 

Back to top

Making sure DB2 Information Integrator is ready for us

At this point, we are ready to roll up our sleeves and see what DB2 Information Integrator has to offer. One thing to note is that during the DB2 Information Integrator installation, you need to select the correct features that correspond to the databases that we want to participate in the federated database setup. A screenshot of the features selection screen is shown below in Figure 2.

After installing DB2 Information Integrator, make sure the environment variable: ORACLE_HOME in the file db2dj.ini located by default in the c:\Program Files\IBM\SQLLIB\cfg directory is set to folder ORA92 of your Oracle installation. A screenshot of the content of our db2dj.ini file is shown in Figure 3:

Another thing we need to make sure is that the Federated Database System Support is enabled - the default is that federated support is enabled for DB2 Information Integrator. We can check this by issuing the command:

 
db2 => GET DATABASE MANAGER CONFIGURATION 

The screenshot below in Figure 4 shows that we are ready to move on:

Creating a wrapper

We need to create a wrapper that will teach our DB2 database how to refer to any Oracle databases we might have. We do this with the following syntax:

 
db2 => CREATE WRAPPER "FEDORACLELITTLE" 
LIBRARY 'db2net8.dll' 

In the statement above, we define a wrapper named FEDORACLELITTLE; we also specify the wrapper library for accessing our Oracle data sources, namely db2net8.dll.

You can also use the DB2 Control Center to perform the work. Do so by right clicking on the Federated Database Objects folder of the BGAUTODB database and choosing Create Wrapper as shown in Figure 5.

As shown in Figure 6, specify the Wrapper name of FEDORACLELITTLE and the library name of db2net8.dll.

Defining a server

Now we need to issue a CREATE SERVER statement to register our Oracle data source as a server within our federated database setup:

 
db2 => CREATE SERVER "fedoracle" TYPE ORACLE VERSION '9i' 
WRAPPER "FEDORACLELITTLE" OPTIONS (NODE 'ORACLE.THINKER') 

In the syntax above, we are following the general syntax of:

 
CREATE SERVER "<i>oraserver</i>" TYPE <i>datasource</i> VERSION '<i>x.y</i>' 
WRAPPER "<i>wrappername</i>" OPTIONS (NODE '<i>net_service_name</i>') 
 

• oraserver is the name by which our Oracle database is known to the DB2 federated server.
• datasource refers to the type of data source server we are configuring access to. In our case, we are using Oracle.
• x.y refers to the version of the Oracle database server that we want to access.
• wrappername refers to the name we specified in the CREATE WRAPPER statement earlier in the article.
• net_service_name refers to the Net Service Name present in the tnsnames.ora file we looked at earlier.

If you are using the Control Center, right click on the Server folder of the FEDORACLELITTLE wrapper and choose Create... as shown here:

As shown in Figure 8, specify the information for the Oracle server. ORACLE.THINKER refers to the Net Service Name present in the tnsnames.ora file we looked at earlier.

Creating a user mapping

Our DB2 user needs to know how to act like an Oracle user so it can interact with our Oracle data source. To do this, we issue the following statement:

 
db2 => CREATE USER MAPPING for "DB2ADMIN" SERVER 
 "fedoracle" OPTIONS(REMOTE_AUTHID 'oracleuser', 
 REMOTE_PASSWORD 'oraclepass') 

In the syntax above, oracleuser and oraclepass are the credentials by which our remote Oracle server can be accessed.

If using the Control Center, right click on the User Mappings folder under the remote data source name of fedoracle as shown below and choose Create...

Next, choose the local user ID that you want to map, and specify user ID and password of the remote user ID We chose to map the local user ID of DB2ADMIN to oracleuser, as shown in Figure 10.

Creating a nickname

Now we need to map DB2 to our Oracle remote tables using the user mapping we just created in the previous section:

 
db2 => CREATE NICKNAME "DB2ADMIN"."FEDLITTLECARRENTTABLE" 
FOR "fedoracle"."ORACLEUSER"."LITTLECARRENTTABLE"

Using the syntax above, we mapped a table named FEDLITTLECARRENTTABLE to our Oracle table named LITTLECARRENTTABLE.

If you are using the DB2 Control Center, right click on the Nicknames folder and choose Create... as shown below:

In Figure 12, you can create a filter to narrow down the remote table names that you will be allowed to map to a nickname. Click OK.

In Figure 13, we have picked the following table: DB2ADMIN.LITTLECARRENTABLE

Making sure we are federated

At this point, we are ready to access our Oracle table as if it were a local DB2 table. We issue the command:

 
db2 => SELECT * FROM FEDLITTLECARRENTTABLE 

Now let's do some federated testing. Use the SQL Plus utility of Oracle to perform an insert:

 
SQL> INSERT INTO LITTLECARRENTTABLE VALUES(1000,360.00,'Oracle 
Washere'); 

And let's go back to DB2 and perform an insert:

 
db2 => INSERT INTO BIGCARRENTTABLE VALUES(1234,234.50,'DB2 Washere') 

Now, we can see in the Figure 15 and Figure 16 that our DB2 and Oracle inserts have successfully populated our BIGCARRENTABLE and FEDLITTLECARRENTTABLE tables.

Back to top

Getting it together

Now we can create a view that lets us see the consolidated contents of both of companies, thereby effectively fulfilling our business need:

 
db2 => CREATE VIEW CONSOLIDATED (CARID,ACCOUNT_BALANCE,CUSTOMER) AS 
(SELECT  CAR_ID,ACCOUNT_BALANCE,CUSTOMER FROM BIGCARRENTTABLE UNION 
SELECT RENTALNUMBER,RENTALCHARGES,CUSTOMERNAME FROM 
FEDLITTLECARRENTTABLE) 

We can then query that view from our federated server:

 
db2=> select * from consolidated 

See the result in Figure 17.

Back to top

Recap

In this article, you learned how to surface an existing Oracle database as a DB2 federated database object. Using a view, we were able to consolidate in real time the data from the DB2 and Oracle tables to enable us to fill our business need as shown in Figure 18.

Back to top

Conclusion

Many firms use a myriad of software offerings. In such setups we might see a number of information and content stores. Tearing down all of these stores and using one integrated database in many cases is not a pragmatic approach. As today's tough economy seems to have a stranglehold on IT budgets, it becomes paramount that companies use as much of their existing IT infrastructure to carry on their business. DB2 Information Integrator offers practicality in a world of disparity.

Back to top

Acknowledgement

The authors would like to thank Micks Purnell for his help in developing this article.

Resources

• Haas, Laura and Eileen Lin. "IBM Federated Database Technology"
  
  
• Lurie, Martin. "The Federation - Database Interoperability"
  
  
• Saracco, C. M. "Coping with Disparate Data in Web Applications"
  
  
• Saracco, C. M. "Building Web Components that Access Federated Data"
  
  
• Saracco, C. M. "Building Entity EJBs that Span Federated Data"
  
  
• Saracco, C. M., and T.J. Rieger. "Our Experience with Developing Entity EJBs over Disparate Data Sources"
  
  
• Saracco, C. M., Susanne Englert and Ingmar Gebert. "Performance of DB2 Information Integrator in a J2EE Environment with Multiple Data Sources"
  

About the authors

		Sridhar Reddy Varakala is a graduate student pursing his Master's Degree in Computer Science at the University of Texas at Arlington. His research involves developing a Dynamic Rule Editor in the Event Condition Action paradigm. You can contact Sridhar at mailto:varakalas@yahoo.com?cc=

		Kulvir Singh Bhogal works as a WebSphere consultant, implementing IBM's e-business strategies across the United States. You can contact Kulvir at mailto:kbhogal@us.ibm.com?cc=.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top