0

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 ?

Fish
  • 57
  • 5
  • Are you really running the same query twice? – APC Dec 21 '19 at 18:19
  • Did you consider to use SQL Developer to do this migration? – Romeo Ninov Dec 21 '19 at 18:19
  • I have updated the query. The ask is to use the shell script for migration – Fish Dec 22 '19 at 05:17
  • All major RDBMSs have bulk load/export utilities, essentially for these reasons. Whether it's actually faster, I don't know. Side note: timestamps in DB2 have up to **7** fractional seconds, so your timestamp select may miss some rows in rare cases. Much better to use an exclusive upper-bound: `WHERE update_ts >= '2019-08-17-00.00.00' AND update_ts < '2019-08-18-00.00.00'` – Clockwork-Muse Dec 23 '19 at 03:33
  • Your question asks "how can I improve performance", but you do not give any details about *where* is the bottleneck. Is the query on Db2 fully indexed? Is the network slow? Is Oracle logged-insert optimally configured? Are all three the issue? You can use a script *and* use bulk unload from Db2 along with bulk load into Oracle, you don't have to use logged inserts... – mao Dec 23 '19 at 12:04

0 Answers0