Problem solve Get help with specific problems with your technologies, process and projects.

Migrating data from DB2 to 9i

We are working on a data migration project that requires data to be transferred from DB2 to Oracle 9i. The two ways we could think of are:
  1. Create flat files from DB2 and then load it to Oracle 9i.
  2. Use a tool to convert DB2 data to Oracle 8i, and then use SQL queries to populate into 9i. (The data model is being changed in Oracle 9i.)
Could you suggest any other solution, or suggest which of the above two is more efficient?

I'd suggest that you look into Oracle's Heterogeneous Services. Here's an excerpt from the Oracle 9.2 Heterogeneous Connectivity Administrator's Guide:

"Generic Connectivity is intended for low-end data integration solutions requiring the ad hoc query capability to connect from an Oracle database server to non-Oracle database systems. Generic Connectivity is enabled by Oracle's Heterogeneous Services component, allowing you to connect to non-Oracle systems with improved performance and throughput.

Generic Connectivity is implemented as either a Heterogeneous Services ODBC agent or a Heterogeneous Services OLE DB agent. An ODBC agent and OLE DB agent are included as part of your Oracle system. Be sure to use the agents shipped with your particular Oracle system, installed in the same $ORACLE_HOME.

Any data source compatible with the ODBC or OLE DB standards described in this chapter can be accessed using a Generic Connectivity agent."

This would be the "tool" you noted in way #2 of your question.

The other option you listed in #1 could be implemented by using SQL*Loader.

The determining factors should likely be the size of your database that you are migrating and which method feels most comfortable to you to implement. Since this is going to be a one-time shot (at least that's what I'm assuming), I wouldn't spend hours trying to figure out which method would take 30 minutes less! Either way you mention will work satisfactorily.

Good luck!

For More Information

Dig Deeper on Oracle database export, import and migration