Fastest way to replicate data from DB2 to Oracle
We need to replicate a large amount of data (about 20 million records) daily from DB2 to Oracle. What is the fastest way to do this? Currently we export/import flat files. Would Oracle Transparent Gateways be faster?
You can try to use Heterogeneous Services (the new name for Transparent Gateways), but the only way to know if this will be fast enough is to test it out for your specific needs.
Aside from that, I would recommend using SQL*Loader to quickly load data from a text file into an Oracle database. You can perform a Direct Path Insert with SQL*Loader, which is much faster than a conventional insert. Another idea to try is to use External Tables (available in Oracle 9i and 10g). External tables use the same SQL*Loader engine, but you can interact with the data directly using SQL statements. In any case, testing will determine if these methods will be fast enough.