Hi Mirza,
You may do this from a host with DB2 server/client & Oracle sqlplus are configured and have an access to a DB2 target & Oracle source respectively.
Below is an example for a single table.
Assume that you have created properly a DB2 table corresponding ALL_TABLES Oracle view.
The sqlplus file would look like this:
alter session set nls_timestamp_format = 'YYYY-MM-DD-HH24.MI.SS.FF6';
alter session set nls_date_format = 'YYYY-MM-DD';
set markup csv on;
set heading off;
set feedback off;
spool all_tables.csv;
select * from all_tables;
spool off;
exit;
You run it as follows to export data to CSV:
sqlplus user/password@TNSNAME @all_tables_ora_export.sql
Then you have a number of options to load data from this file into your target DB2 database with DB2 CLP:
db2 connect to mydb2db
# Using DB2 IMPORT command:
db2 import from all_tables.csv of del modified by compound=100 replace into all_tables
# Using DB2 LOAD command:
db2 load client from ${PWD}/all_tables.csv of del replace into all_tables nonrecoverable
# Using DB2 INGEST command:
# Run it only once if you haven't ever used INGEST with this database
db2 "CALL SYSPROC.SYSINSTALLOBJECTS('INGEST', 'C', 'USERSPACE1', NULL)"
# Data load:
db2 ingest from file all_tables.csv format delimited replace into all_tables
All DB2 data load methods have their pros and cons. More on these commands is in the DB2 docs.
LOAD from the DB2 server (you may omit the CLIENT clause in this case) is the fastest with large data volumes generally. LOAD from a remote client (w/ CLIENT as in the example) is not so fast.
LOAD (~ Oracle SQL*Loader with direct-path load) requires much less logging since it's low level.
INGEST & IMPORT are sql-based
INGEST is a good alternative to LOAD
You may use a pipe instead of a file as well.
In this case:
- You create a pipe with the following command beforehand (you may use the same pipe for multiple non-parallel data transfers):
mkfifo all_tables.csv
- and run data export in a background (notice the ampersand at the end of the command):
sqlplus user/password@TNSNAME @all_tables_ora_export.sql &