Topic
3 replies Latest Post - ‏2012-07-20T15:04:44Z by SystemAdmin
SystemAdmin
SystemAdmin
5837 Posts
ACCEPTED ANSWER

Pinned topic db2 load vs oracle sqlldr - performance suggestions?

‏2012-07-18T23:32:21Z |
Hi.

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.

Regards.
Updated on 2012-07-20T15:04:44Z at 2012-07-20T15:04:44Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    5837 Posts
    ACCEPTED ANSWER

    Re: db2 load vs oracle sqlldr - performance suggestions?

    ‏2012-07-18T23:35:08Z  in response to SystemAdmin
    Forget to mention: I ran load command with nonrecoverable option.
    • SystemAdmin
      SystemAdmin
      5837 Posts
      ACCEPTED ANSWER

      Re: db2 load vs oracle sqlldr - performance suggestions?

      ‏2012-07-20T07:55:18Z  in response to SystemAdmin
      Hello Leo,

      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 :
      db2set DB2_PARALLEL_IO=*
      - 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.
      Yves-Antoine Emmanuelli
      • SystemAdmin
        SystemAdmin
        5837 Posts
        ACCEPTED ANSWER

        Re: db2 load vs oracle sqlldr - performance suggestions?

        ‏2012-07-20T15:04:44Z  in response to SystemAdmin
        Thanks Yves.

        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.

        Regards.