Solved

How to Query on Multiple Databases (Oracle and DB2)

Posted on 2009-05-18
8
1,126 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
ID: 24412380
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
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
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 150 total points
ID: 24412551
0
 

Author Comment

by:objectkk
ID: 24415808
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 250 total points
ID: 24417651
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
ID: 24417714
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
ID: 24423414
Tom is correct,
you will need to buy websphere information integrator

0
 

Author Closing Comment

by:objectkk
ID: 31582592
Thank you guys. That was helpful.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…

910 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

16 Experts available now in Live!

Get 1:1 Help Now