Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: db2 huge data insert

    Hi,
    I am writing a java program that needs to insert a huge amount of records into a table( more than 100000 records). I use JDBC to access the DB2 database. Currently I use the following piece of code to insert the records:

    PreparedStatement pstmt = con.prepareStatement("INSERT INTO MY_TABLE (FIELD1, FIELD2) VALUES(? , ?)");

    for (int i = 0; i < n; i++) {

    pstmt.setInt(1, i);
    pstmt.setString(2, "NAME");
    pstmt.executeUpdate();

    if (i % 500 == 0) {
    con.commit();
    }
    }

    I was wondering about a faster way to make this inserts. For example something like a bulk data insert: first I prepare the lines
    to insert and them I pass them to the DB in one shot. Is it possible to do that in java ?

    Thanks in advance

    jad72

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: db2 huge data insert

    Why not use load or import utilities ?

    If you want to do it in Java, then you can try buffered inserts and multirow inserts ...

    HTH

    Sathyaram

    Originally posted by jad72
    Hi,
    I am writing a java program that needs to insert a huge amount of records into a table( more than 100000 records). I use JDBC to access the DB2 database. Currently I use the following piece of code to insert the records:

    PreparedStatement pstmt = con.prepareStatement("INSERT INTO MY_TABLE (FIELD1, FIELD2) VALUES(? , ?)");

    for (int i = 0; i < n; i++) {

    pstmt.setInt(1, i);
    pstmt.setString(2, "NAME");
    pstmt.executeUpdate();

    if (i % 500 == 0) {
    con.commit();
    }
    }

    I was wondering about a faster way to make this inserts. For example something like a bulk data insert: first I prepare the lines
    to insert and them I pass them to the DB in one shot. Is it possible to do that in java ?

    Thanks in advance

    jad72
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: db2 huge data insert

    Originally posted by jad72
    Hi,
    I am writing a java program that needs to insert a huge amount of records into a table( more than 100000 records). I use JDBC to access the DB2 database. Currently I use the following piece of code to insert the records:

    If you absolutely must use Java you could try batch updates. There was a discussion recently about this methods (and some possible issues)

    http://www.dbforums.com/t988822.html

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: db2 huge data insert

    A recent IBM Dev Domain Article was about INSERT PErformance

    http://www-106.ibm.com/developerwork...ins/index.html

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2002
    Location
    USA
    Posts
    53
    Don't use values(?,?), pstmt.setInt(1, i);pstmt.setString(2, "NAME");

    Try

    Strint st = "INSERT INTO MY_TABLE (FIELD1, FIELD2) VALUES(2 , 'NAME')";
    PreparedStatement pstmt = con.prepareStatement(st);
    for (int i = 0; i < n; i++) {
    pstmt.executeUpdate();
    if (i % 500 == 0) {
    con.commit();
    }
    }

    I think this is faster.

    HTH

    -ljrong

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    IMHO, with (2,'NAME') performance is lower because each time you issue a INSERT Statement, you'll have to PREPARE ...

    When you use (?,?), you prepare only once

    If this INSERT is going to be a regular one, don't forget to increase the LOGBUFSZ and DBHEAP db cfg parameters ... They will make a big difference

    Cheers



    Sathyaram

    Originally posted by ljrong
    Don't use values(?,?), pstmt.setInt(1, i);pstmt.setString(2, "NAME");

    Try

    Strint st = "INSERT INTO MY_TABLE (FIELD1, FIELD2) VALUES(2 , 'NAME')";
    PreparedStatement pstmt = con.prepareStatement(st);
    for (int i = 0; i < n; i++) {
    pstmt.executeUpdate();
    if (i % 500 == 0) {
    con.commit();
    }
    }

    I think this is faster.

    HTH

    -ljrong
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Mar 2004
    Posts
    3
    Thanks a lot for your advices.
    I tried using batch updates by buffeing multiple inserts in one shot and I obtained a substantial increment in the performances.

Posting Permissions

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