CODEWITHSUNDEEP
oracle-databasemigrationoracle-sqldeveloperdb2-400
Manuel
Manuel

Reputation: 17

How to create a database link from Oracle database to DB2 on system i

I'm trying to establish a connection to a DB2 database using a database link in Oracle.

The final objective is to be able to do a "create table XXX as select * from YYY@DB2"

I can connect directly to the DB2 database using SQL Developer and the db2jcc.jar connector but i can't create the database link.

Additionaly, when i use the feature "Migrate to Oracle..." on SQL Developer it just dump a java exception.

Thanks in advance, Manuel

Upvotes: 0

Views: 9586

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

One way of doing this is with "heterogenous services", ie, Oracle takes via ODBC to a remote non-oracle data source.

There's a manual for this in the standard doc set:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/heter/index.html

but in a nutshell, the basic steps are:

1) have an ODBC driver for DB2 on your database server, or on a machine that your database server can reach.

2) Configure an ODBC target for that DB2 database. Lets call it "MYDB2"

3) create a file initMYDB2.ora in $ORACLE_HOME/hs/admin. Configure it as per the sample (init4g4odbc.ora). It points to your MYDB2 target.

4) In that directory, you'll also find samples for listener.ora and tnsnames.ora.

So you'll end up with a tnsnames entry called "MYDB2". It will reference the initMYDB2.ora, which will point to the MYDB2 odbc target, which will be your DB2 database.

Then you create a database link in the normal way:

create database link blah
connect to my_db2_user
identified by my_db2_password
using 'mydb2';

Hope this helps.

Upvotes: 1

Related Questions