I am doing large INSERTs into an old DB2 server, and for various reasons, I need them to be as fast as possible.

According to https://developer.ibm.com/articles/dm-0403wilkins/ one of the most efficient ways to improve INSERT speed is to use prepared statements with array inserts. So that's what I'm trying.

This is the code:

    String sql="INSERT INTO MY_DATABASE (ID, COL1, COL2, NAME, TAG) VALUES (?, 'LO', 'MI', ?, ?)";

    Integer[] ids = [I create an array of Integers here, this code is irrelevant.]
    String[] names = [I create an array of Strings here, this code is irrelevant.]
    String[] tags = [I create an array of Strings here, this code is irrelevant.]

    try {
        Array idArray = conn.createArrayOf("INTEGER",ids);
        Array nameArray = conn.createArrayOf("CHAR",names);
        Array tagArray = conn.createArrayOf("CHAR",tags);

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setArray(1,idArray);
        ps.setArray(2, nameArray);
        ps.setArray(3, tagArray);

        ps.executeUpdate();

    }catch(SQLException sqle) {
        log.warning("Prepared statement failed: " sqle);
    }

All lines execute nicely, except the execution of the Prepared Statement. I get this error:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][1091][10417][4.23.42] Invalid data conversion: Parameter instance com.ibm.db2.jcc.am.o@f736069 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

What I have checked:

The columns in MY_DATABASE have the TYPE_NAMEs: INTEGER (10), CHAR (10), CHAR (2), CHAR (15), CHAR (1), respectively. Nothing wrong there.

Similar questions here on StackOverflow with this errorcode and sqlstates have been about using smallints and integers that go above the thresholds, and don't apply to my use of Arrays.

I have seen that I can probably achieve ABOUT the same thing with looping through my arrays, setting the parameters one by one, and then do ps.addBatch(), but from what I read on the IBM link, it appears that inserting with sql-arrays like this is almost 5 times faster, so I would want this to work.

Now, "com.ibm.db2.jcc.am.o@f736069" looks very much like a String output of an object that doesn't have a toString() method. Also, it's apparently an object created by the db2 driver. I don't know if it's the driver trying to convert "com.ibm.db2.jcc.am.o@f736069" into an integer, or what it could be.

Unfortunately, I cannot try to omit the Integer-array, as it's a primary key that doesn't auto-increment.

CodePudding user response：

You need to read the documentation at the link you provided...

Use array insert in CLI. This involves preparing an INSERT statement with parameter markers, defining an array for storing the values to be inserted, binding the array to the parameter markers, and executing the insert once for each set of array contents.

You're trying to do the above, but you're not using CLI you're using JDBC. Then the next bullet point...

Use batch operations in JDBC. This is based on the same concept as array inserts in CLI, but with different implementation details. After preparing the insert via the prepareStatement method, the remaining steps are to invoke the appropriate setXXXX method (for example, setString or setInt) for each column, followed by addBatch. Those steps are repeated for each row to be inserted, then executeBatch is invoked to perform the inserts. See the JDBC Tutorial in the References for an example.

Mark provided a nice reference link an the examples show the setXXX, addBatch and executeBatch as discussed above.

try {
  PreparedStatement preps = conn.prepareStatement(    
    "UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");          
  ps.setString(1,mgrnum1);                              
  ps.setString(2,deptnum1);
  ps.addBatch();                                        

  ps.setString(1,mgrnum2);
  ps.setString(2,deptnum2);
  ps.addBatch();
  int [] numUpdates=ps.executeBatch();                  
  for (int i=0; i < numUpdates.length; i  ) {           
    if (numUpdates[i] == SUCCESS_NO_INFO)
      System.out.println("Execution "   i   
        ": unknown number of rows updated");
    else
      System.out.println("Execution "   i   
        "successful: " numUpdates[i]   " rows updated");
  }
  conn.commit();                                        
} catch(BatchUpdateException b) {                       
  // process BatchUpdateException
}

CodePudding user response：

Some analog of CLI array operations described briefly in Binding parameter markers in CLI applications with row-wise array input with an example in ~/sqllib/samples/cli/tbload.c in JDBC are batch operations only. There is no similar technique with arrays in JDBC.

Follow this link instead: Making batch updates in JDBC applications.