How to Query on Multiple Databases (Oracle and DB2)


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.
Who is Participating?
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
To Query from Oracle to Oracle Databases, you have to issue Database LINK across servers.

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
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

objectkkAuthor Commented:
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.

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.

Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
       Here's an example of using Materialized views after installing Oracle Transparent Gateways and configuring DB2 as your remote server.

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.

    Kindly confirm.

Hope this helps.
tliottaConnect With a Mentor Commented:

> ...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 is correct,
you will need to buy websphere information integrator

objectkkAuthor Commented:
Thank you guys. That was helpful.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.