Solved

How to Query on Multiple Databases (Oracle and DB2)

Posted on 2009-05-18
8
1,120 Views
Last Modified: 2012-05-07
Hello,

I have 2 Oracle and 1 DB2 databases. let us say Oracle 1, Oracle 2 and DB2 1.

I need to select a record from Oracle 1 and verify its existance in both Oracle 2 and DB2 1, before returning the result of the query.

All databases have a key that I can use to link them.

so All I am asking how can treat all those 3 databases as one database and query against all of them to get the results I want ?

thank you much in advance, and any suggestions are highly appreciated.
0
Comment
Question by:objectkk
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 250 total points
Comment Utility
To Query from Oracle to Oracle Databases, you have to issue Database LINK across servers.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5005.htm

To query DB2, I guess you have to install Oracle Transparent Gateways.

Transparent Gateways are used to enable access to other data sources as if you are connecting to another Oracle database. Using these Transparent Gateways, you can interact with foreign databases like DB2 or MSSQL Server without worrying about differences in SQL implementation.

Hope this helps
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
you can create a federated server on your db2 that will allow you to access an oracle database from a query you issue against db2
this way you can create 2 federated server on your db2 to allow linking to oracle,
and then in a single query join the three tables
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 150 total points
Comment Utility
0
 

Author Comment

by:objectkk
Comment Utility
rrjegan17,
Thank you for your info from Oracle side.  I talked with our DBA's here and they are also familiar with Oracle to Oracle Link and said they need some time to research on Oracle to DB2 link.
Do we need any special tools to access DB2 Database ? Do you have any example or any link that shows how to do that link between Oracle and DB2.




momi_sabag,
Thank you much for the DB2 link you gave. It gave our DB2 DBAs an idea that it can be done. but you said we can create a FEderated server. But Our DBA's say we have to buy a federated server.

I am confused now, Federated server is a word that refers to a DB Server which is linked with Oracle or it is a special server that we need to buy to be able to access Oracle from DB2.

I dont mind accsing Oracle from DB2 or DB2 from Oracle. but please suggest which way is easy does not require special software installations wich does not come out of the box with Oracle server or DB2 server..

thank you.

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 250 total points
Comment Utility
objectkk,
       Here's an example of using Materialized views after installing Oracle Transparent Gateways and configuring DB2 as your remote server.

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10764/majfeat.htm

And for Federation, Federation is a feature included in Enterprise edition of DB2 only.
For Workgroup edition you need to purchase additional license for it.

I will explain Federation wrt Oracle as you are more familiar in it. In oracle you can query other databases using Database Link DBLINK right. In Oracle DBLINK doesn't supports other RDBMS and you have to use Oracle Transparent Gateways as I mentioned earlier.

In DB2, Database Link is referred as Federation and you have to purchase it for Workgroup edition or have it as part of Enterprise edition.

I hope WebSphere Information Integrator is required for Oracle Database as per the reference below.

http://www-01.ibm.com/software/data/db2/9/editions_features_hff.html

Momi_sabag,
    Kindly confirm.

Hope this helps.
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 100 total points
Comment Utility
objectkk:

> ...please suggest which way is easy does not require special software installations wich does not come out of the box with Oracle server or DB2 server..

The problem is partly that you want to run "a query" that accesses different databases at the same time. When you execute a query, it is processed by some 'database manager'. That database manager has programming that knows how to retrieve from information from within that kind of database.

Which database manager should process your query?

Which database manager knows how to access data from some other vendor's kind of database? How would something "out of the box" from Oracle have any knowledge of how to access a database from IBM? How would something "out of the box" for DB2 know how to access a database from Oracle? IBM knows how to access DB2 databases, and Oracle knows how to access Oracle databases. The two different vendors are in the business of _selling_ access to their databases.

And that brings you to adding additional cost features to access a different vendor's database.

One _potential_ alternative is to do some java programming and perhaps use JDBC drivers for the cross-database access. I _think_ that JDBC drivers are available for both essentially for 'free'; however, there is certainly a 'cost' in terms of programming.

Tom
0
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
Tom is correct,
you will need to buy websphere information integrator

0
 

Author Closing Comment

by:objectkk
Comment Utility
Thank you guys. That was helpful.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now