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

[Frank de Vries]

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

[Massimo Bianchi]

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]

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

[Massimo Bianchi]

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]

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

[Yogeeraj_1]

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

[Dennis Flinn]

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.