I can transfer data from DB2 to Oracle by first unloading to a flat file and then loading it to Oracle using the SQL*LOAD utility. Is there a similar way to transfer data from Oracle to DB2? We cannot use the EXPORT utility of Oracle because the file created by EXPORT utility can only be read by the IMPORT utility of Oracle.
You could use heterogeneous services so that a non-Oracle system (DB2) appears as a single, local, Oracle database; the local Oracle server hides the distribution and heterogeneity of the data. The Oracle server accesses the non-Oracle system using Oracle8i Heterogeneous Services and a system-specific transparent gateway. Therefore, if you include a DB2 database in an Oracle distributed system, you need to obtain a DB2-specific transparent gateway so that the Oracle databases in the system can communicate with it.
The transparent gateway agent facilitates communication between Oracle and non-Oracle databases and uses the Heterogeneous Services component in the Oracle server. The agent executes SQL, procedural, and transactional requests at the non-Oracle system on behalf of the Oracle server.
See the following Oracle docs for detailed information (all docs can be found at http://technet.oracle.com):
- Oracle8i Concepts
- Oracle8i Distributed Database Systems
- Oracle Open Gateway Installation and User's Guide
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.