How to Query on Multiple Databases (Oracle and DB2)

Posted on 2009-05-18
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
  • 3
  • 2
  • 2
  • +1
LVL 57

Accepted Solution

Raja Jegan R earned 250 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 150 total points
ID: 24412551
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


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 250 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 100 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
iSeries DB2 SQL - Request user input 12 61
Set Warning Thresholds on Database mirroring 4 57
Exchange 2016 Databse move 5 65
VBA MS Word "Table of Contents" extractor` 4 57
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…

830 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