Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

How to import Oracle export dmp into DB2, which tools ?

Frank de Vries
Respected Contributor

How to import Oracle export dmp into DB2, which tools ?

Hi,
We like to export a data-model schema from
Oracle and import it into DB2.

Unfortunately when we use Oracles native export
tools we get a binary format dump file.

Anyone knows of a way around to able to achieve
what we want to do i.e. get the data and schema
in DB2 ?

Thanks
Look before you leap
6 REPLIES
Massimo Bianchi
Honored Contributor

Re: How to import Oracle export dmp into DB2, which tools ?

Hi, I found this metalink note that may be helpfull, but it looks there are problems.

Doc ID: Note:1007811.6
Subject: ACCESSING ORACLE DATABASE FROM WITHIN DB2 USING TRANSPARENT GATEWAY
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 23-FEB-1995
Last Revision Date: 14-MAY-2002


Problem Description: ==================== Is it possible to access an Oracle database from within DB2 using an Oracle transparent gateway?


Problem Explanation: ==================== The purpose of a transparent gateway is to allow the Oracle database access to the DB2. Is the opposite also true?

Solution Description: ===================== Oracle's Gateway products let Oracle clients communicate with the server side of DB2, but aren't in a position to let DB2 clients communicate with the server side of Oracle.

Solution Explanation: ===================== The Gateways make "foreign data stores" like DB2 look like Oracle databases to an Oracle client. If a DB2 client wants a product that makes Oracle look like a DB2 database, IBM will need to provide it. At a "very high" level, it may seem, "Oracle has opened this pipe between Oracle and DB2 ... can't a DB2 client use the pipe, too?" But look one level lower and it's easy to see why the answer is, "No." The Gateway takes SQL in an internal format that Oracle expects, converts it to a format that DB2 expects, and presents that SQL to the server side of DB2. And if the server side of DB2 provides data in response, the Gateway does any conversions required so the Oracle client can work with it. The plight of a DB2 client with SQL in an internal format that DB2 expects is outside the scope of what Oracle Gateways were designed to handle.


HTH,
Massimo
twang
Honored Contributor

Re: How to import Oracle export dmp into DB2, which tools ?

Take a look at this link, it may help you:
http://www.ispirer.com/products
Massimo Bianchi
Honored Contributor

Re: How to import Oracle export dmp into DB2, which tools ?

The only solution i can think of is outputting the content of tables in a csv format, and then import into db2 with an utility like oracle sqloader.

HTH,
Massimo
twang
Honored Contributor

Re: How to import Oracle export dmp into DB2, which tools ?

A migration example, you should find it very useful:
http://www.redbooks.ibm.com/redbooks/SG245648.html
Yogeeraj_1
Honored Contributor

Re: How to import Oracle export dmp into DB2, which tools ?

hi,

If you have IBM product DataJoiner, you can do the following:

On the DJ machine, define a nickname (NICK1) for the Oracle. Define a nickname (NICK2) for the DB2 UDB database.

Connect to the DJ database and do the following:
create table NICK2.table2 as select * from NICK1.table1

This might take some time if you have a lot of data to import. In that case, you probably should choose method A.

hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Dennis Flinn
Advisor

Re: How to import Oracle export dmp into DB2, which tools ?

When you use the term "data-model schema"
are you referring to the DDL or the acutal data? If you want simply the DDL you could export without the data and have a file containing the sql statements to build the tables, indexes, and etc. I believe you would then edit that file to meet the DDL on a DB2 environment.