DB2 migration to Oracle using Shell Script

177 Views Asked by At

I am migrating data from DB2 to Oracle in a daily basis.I have created identical table in oracle (destination) as in DB2 (source). I can able to migrate the data from DB2 to Oracle with out any issues but it takes almost 40 minutes to migrate 15,000,000 rows.

Below is the shell script,

sqlplus -s  

set echo off
set heading off
set pagesize 10000
set arraysize 500

insert into transaction(tran_code,tran_dt,tran_num,tran_amt,update_ts)
select (tran_code,tran_dt,tran_num,tran_amt,update_ts) 
from smtp.transaction@db2 
where update_ts between '2019-08-17-00.00.00.000000' and '2019-08-17-23.59.59.999999' and tran_code <= 'A111';

insert into transaction(tran_code,tran_dt,tran_num,tran_amt,update_ts)
select (tran_code,tran_dt,tran_num,tran_amt,update_ts) 
from smtp.transaction@db2 
where update_ts between '2019-08-17-00.00.00.000000' and '2019-08-17-23.59.59.999999' and tran_code > 'A111' and tran_code <= 'BC11';

exit
EOF

Whether the performance cab improved by setting proper values for pagesize and arraysize ? or is there any other way to increase the performance ?

0

There are 0 best solutions below