Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    18

    Unanswered: Confused about commit

    DBAs,

    I have 70,000 XML files to load into a database table. Each file average 3 KB in size.

    Suppose I create the following loop in a shell script

    From 1 to 70,000
    Load file into database (insert into…) using Java based ETL tool.
    Done

    The ETL process is itself minimal. Some string truncation, some concatenation.

    Should I commit after each successful INSERT statement or should I commit when I have loaded all 70,000 files? Which would be faster?

    PS: Our shop runs IBM DB2 v9.7.

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    On average, how many rows will be inserted for each xml file read?

    Is a "bulk load" available to you? A bulk load will process faster than so many individual inserts. With a bulk load, you could quiesce this database, back it up, load the xml data, and bring the system back up (after verifying all is well).

    If you use a bulk load, there would be no concern about commit.

  3. #3
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Should I commit after each successful INSERT statement or should I commit when I have loaded all 70,000 files?
    are those your only options?
    Dick Brenholtz, Ami in Deutschland

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you need to do inserts, I would recommend using the IMPORT command. You can control commits with COMMITCOUNT. The LOAD command is a faster option, but has done concurrency disadvantages.

    If you need to do inserts with data that large, I would commit every few hundred, otherwise you may fill up your active logs.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •