Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to Query on Multiple Databases (Oracle and DB2)

Posted on 2009-05-18
Medium Priority
Last Modified: 2012-05-07

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.
Question by:objectkk
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 57

Accepted Solution

Raja Jegan R earned 1000 total points
ID: 24412380
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
LVL 37

Expert Comment

ID: 24412524
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
LVL 37

Assisted Solution

momi_sabag earned 600 total points
ID: 24412551
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 24415808
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.

LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1000 total points
ID: 24417651
       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.
LVL 27

Assisted Solution

tliotta earned 400 total points
ID: 24417714

> ...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.

LVL 37

Expert Comment

ID: 24423414
Tom is correct,
you will need to buy websphere information integrator


Author Closing Comment

ID: 31582592
Thank you guys. That was helpful.

Featured Post

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Let’s face it: one of the reasons your organization chose a SaaS solution (whether Microsoft Dynamics 365, Netsuite or SAP) is that it is subscription-based. The upkeep is done. Or so you think.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

730 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