In my job I have to test the time could spend an data pumping from AS400 to Oracle database.
Data extracted from AS400 is put (using JTOpen's KeyedFile object) in a CSV file, that file is transferred to linux box usig ftp then pumped into Oracle database using sqlldr.
sqlldr took less than 3 minutes to load 3.3M records (using direct path) in local (in my laptop) XE database.
Since I just started to use DB2, I loaded (load command) the same CSV file into local db2 instance. This load took more than 5 minutes. Note that not only was slower than sqlldr but oracle table has 1 index which i didn't replicated in db2 database. Note that I tried to compare similar conditions:
1. Same table structure.
2. Same CSV file.
3. In each database I created the same tablespaces for the test table.
4. Both database servers runs in the same hardware (my laptop) and runs exclusively (when Oracle is up, db2 is down and viceversa).
5. sqllrd commits after 10000 records.
Do I have to assume that Oracle's sqlldr (hope only such utility) is better than db2's load or do I missing some performance arguments in db2's loader?
BTW, how can I skip rows with db2's load command? First row contains column names.
Pinned topic db2 load vs oracle sqlldr - performance suggestions?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-07-20T15:04:44Z at 2012-07-20T15:04:44Z by SystemAdmin
Re: db2 load vs oracle sqlldr - performance suggestions?2012-07-20T07:55:18ZThis is the accepted answer. This is the accepted answer.
- SystemAdmin 110000D4XK
You might have to check/modify the following :
- increase UTIL_HEAP_SZ to get full load parallelism (look at the load output to see if it is necessary).
- activate parallel I/O for the tablespaces :
- set the bufferpool size as automatic.
The header line is likely to be rejected by LOAD, but I don't think there is any way to skip the first line of a DEL file.
Hope this helps.
Re: db2 load vs oracle sqlldr - performance suggestions?2012-07-20T15:04:44ZThis is the accepted answer. This is the accepted answer.
- SystemAdmin 110000D4XK
I dropped the table then loaded again and got the almost same speed without any parameter changed.
I guess that the difference was in the physical storage extension: With Oracle I created the tablespace sized according to the data to be loaded. In the case of db2 I use automatic storage.