Jdbc batch insert fails all statements after failed statement in Db2 i

Question

I am inserting records in a table in Db2-400 (Db2 i) database using JDBC prepared statement batch. Table has primary key in it.

When any statement in batch has duplicate key, then it throws BatchUpdateException (which is expected). But when there is a BatchUpdateException, it fails all further statements in batch.

Suppose batch has 5 statements. Exception is at 3rd statement only. Then 4th and 5th statement also fails.

It is unlike Db2 LUW, which successfully executes all statements after that failed statement.

Example:

UQTEST table has first column primary. Code is adding key as 2 when value of i is 3 to make key duplicate for a statement.

    PreparedStatement stmt = conn.prepareStatement("INSERT INTO  LIBTEST.UQTEST VALUES(?, ?)");

     for (int i = 1; i <=5; i++)
     {

         if (i ==3)
         {
            stmt.setInt(1, 2);

         }
         else
         {
            stmt.setInt(1, i);

         }
         stmt.setString(2, "Abc");
         stmt.addBatch();
     }

     try
     {

          stmt.executeBatch();
     }
     catch(BatchUpdateException ex)
     {
         int[] updatecounts = ex.getUpdateCounts();
         System.out.printlns(updatecounts);
     }

After running this code, only 2 records inserted in table as:

   1 Abc
   2 Abc

I am using jt400.jar and JDBC driver used is :

com.ibm.as400.access.AS400JDBCDriver()

I want all those statements to be executed successfully which does not have any exception.

What is the autocommit mode of the connection, you're using? — Lothar, Feb 08 '19 at 09:57
My auto commit is by default true. I did not changed it. I tried by setting it false. But in this case nothing is inserted as there is exception in executeBatch() and conn.commit() is never called. Even calling conn.commit in catch also doesn't work. — Abhishek Tiwari, Feb 08 '19 at 09:59
What happens if you turn off autocommit mode (the suggested mode according to the ToolBox's javadoc)? — Lothar, Feb 08 '19 at 12:35
Dont know if is same, but for SQLite you need set autocommint to fasle, execute batch, then set autocommit to true — Raso, Feb 08 '19 at 12:52
@Lother As mentioned in above comment, By turning off autocommit doesn't work because: I have stmt.executeBatch() first and then conn.commit(). If exception is there in executeBatch(), nothing is inserted because commit never happened. Placing conn.commit() in catch block also doesn't work. — Abhishek Tiwari, Feb 08 '19 at 13:27
@Raso I believe, it is required to call connection's commit method to make records inserted in table. Just setting auto commit true again doesn't work. — Abhishek Tiwari, Feb 08 '19 at 13:33

Jdbc batch insert fails all statements after failed statement in Db2 i

0 Answers0