Re: Oracle to DB2 migration

venkatprakash_at_hotmail.com (Prakash) wrote in message news:<78b3b5e2.0308201501.6a8b0a24_at_posting.google.com>...
> Hi
>
> We are currently working on a project to migrate our Oracle database
> to DB2. For migrating the data, we are using Oracle Select statement
> which will write the data into a text file. Then we use Db2 Loader to
> load the data into DB2. All the tables and columns are same in oracle
> and DB2.
>
> My question is: It takes long time (about an hour for 4 million rows)
> for the Oracle select and write into the file. I suppose almost same
> time will be taken for loading also. Our application is 24x7. So, I am
> wondering is there any speedy way to get this done. There may be a
> possibility for shutting down the application for some time (may be 4
> hours) but not more than that.
>
> Thanks for your help
>
> V Prakash

LOAD ought to be very fast provided your DB2 environment is set up okay; would worry more about the extract times. Options that might help would be performing multiple extracts at once - even from the same table (program cursors tend to be a bottleneck and that's one way to overcome this); or if your Oracle application reliably timestamps row inserts/updates, maybe you could do two extracts. The first would run while the Oracle system is online, extracting all records inserted/updated since the earliest ever time, and then loading them to DB2 with the LOAD utility. The second essentially a repeat, but with Oracle system offline and only extracting records inserted/updated since the start of the previous run - these then loaded to DB2 using IMPORT with INSERT_UPDATE mode - should be quick because volumes would presumably be much lower. You could still stream the extracts to minimise downtime even more.

Oracle people should be able to advise best on maximising speed of Oracle extract to file.

DG Received on Thu Aug 21 2003 - 18:56:51 CDT