Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    6

    Unanswered: DB2 - Java addBatch Problem

    I am using DB2 7.2 on Windows 2000 and Java 1.3

    I use a preparedStatement to insert a lot of records so I use the addBatch statement. The performance is good when using addBatch but in some rows a column is changed :|, I mean, supouse record 1 as
    column1_1 column1_2 column1_3

    and record 2 as

    column2_1 column2_2 column2_3

    after the inserts using batch I got somting like

    column1_1 column2_2 column1_3
    column2_1 column2_2 column2_3

    It happends with some rows only. If I use prepStmt.executeUpdate() instead of prepStmt.addBatch() it works ok, but slower

    Any Idea ?.

    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: DB2 - Java addBatch Problem

    Are you using type 3(net) or type 2(app) driver?
    What is the data type of the columns, chars?

    We had a problem with jdbc driver (type 3) when under some circumstances we would get "error receiving from socket" or code will "hang" for a long period of type. We found out that there was a bug in IBM driver, where code incorreclty reallocated string based data types. We recived a special build.

    I suggest you contact IBM support.

  3. #3
    Join Date
    Jan 2003
    Posts
    6
    I am using type 3 and the column that has the problem is a char, so, maybe is what you say.

    Thanks

  4. #4
    Join Date
    Jan 2003
    Posts
    6
    I am not sure if I was wrong or IBM driver. I used the batch like this:

    for(int i=1;i<totalRows;i++){
    .
    .
    .
    stmt.addBatch();
    if (i % 10000 == 0) {
    stmt.executeBatch();
    }
    }
    .
    .

    so that each 10000 rows process the batch and continue with next rows.

    I changed it to:

    if (i % 10000 == 0) {
    stmt.executeBatch();
    stmt.close();
    stmt=con.prepareStatement(query);
    }

    And it works. I am not sure why do I have to close the preparedStatement, maybe a bug on DB2 driver, maybe somewhere on documentation say that, who Knows....?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by jgn78
    I am not sure if I was wrong or IBM driver. I used the batch like this:

    for(int i=1;i<totalRows;i++){
    .
    .
    .
    stmt.addBatch();
    if (i % 10000 == 0) {
    stmt.executeBatch();
    }
    }
    .
    .

    so that each 10000 rows process the batch and continue with next rows.

    I changed it to:

    if (i % 10000 == 0) {
    stmt.executeBatch();
    stmt.close();
    stmt=con.prepareStatement(query);
    }

    And it works. I am not sure why do I have to close the preparedStatement, maybe a bug on DB2 driver, maybe somewhere on documentation say that, who Knows....?
    Do you call stmt.clearParameters() before setting new parameters in the loop?

  6. #6
    Join Date
    Jan 2003
    Posts
    6
    Yes, I tried this after each executeBatch() but It only works if I close the statemant or if I use the executeBatch() only at the end of the loop (only once).

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by jgn78
    Yes, I tried this after each executeBatch() but It only works if I close the statemant or if I use the executeBatch() only at the end of the loop (only once).
    I think you need to use it before each addBatch(), not after the executeBatch():
    PHP Code:
    stmt conn.prepareStatement(...);
    for (...) {
       
    stmt.clearParameters();
       
    // set statement parameters
       
    stmt.setInt(...);
       
    stmt.setString(...);
       ...
       
    stmt.addBatch();
       ...


  8. #8
    Join Date
    Jan 2003
    Posts
    6
    nope, same thing. I used as you said but, again, if I use the executeBatch() more than once, with out closing it, it change a column. The column is char(50).

    Code:
    for (int i = 1; elementos.hasMoreElements(); i++) {
    	stmt.clearParameters();
                    Hashtable hash = (Hashtable) elementos.nextElement();
    	
                    String nombreTrab = hash.get("NOMBRE_TRAB").toString();
                    nombreTrab = nombreTrab.replace('/', '#');
    
                    stmt.setString(1, (String) hash.get("NSS"));
                    stmt.setString(2, (String) hash.get("CURP"));
                    stmt.setString(3, (String) hash.get("JORNADA"));
                    //This is the one that fails.
                    stmt.setString(4, (String) nombreTrab.trim());
                    stmt.setInt(5, Integer.parseInt((String) hash.get("NUM_MOV")));
                    stmt.setString(6, (String) hash.get("RFC_ASEG"));
                    stmt.setDouble(7, Double.parseDouble(agregaPunto(hash.get("ULTIMO_SDI").toString())));
                    stmt.setInt(8, Integer.parseInt((String) hash.get("TIPO_TRAB")));
                    stmt.addBatch();
    
                    if (i % 1000 == 0) {
                        stmt.executeBatch();
                        //If uncomment this, works.
                        //stmt.close();
     	    //stmt = adminBd.getPreparedStatement(sql);
                    }
                }

Posting Permissions

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