- Create flat files from DB2 and then load it to Oracle 9i.
- 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.)
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.
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.