Blocked inserts with JDBC

You can use a blocked insert operation to insert several rows into a database table at a time.

A blocked insert is a special type of operation on IBM i that provides a highly optimized way to insert several rows into a database table at a time. Blocked inserts can be thought of as a subset of batched updates. Batched updates can be any form of an update request, but blocked inserts are specific. However, blocked insert types of batched updates are common; the native JDBC driver has been changed to take advantage of this feature.

Because of system restrictions when using blocked insert support, the default setting for the native JDBC driver is to have blocked insert disabled. It can be enabled through a Connection property or a DataSource property. Most of the restrictions when using a blocked insert can be checked and handled on your behalf, but a few restrictions cannot; thus, this is the reason for turning off blocked insert support by default. The list of restrictions is as follows:

  • The SQL statement used must be an INSERT statement with a VALUES clause, meaning that it is not an INSERT statement with SUBSELECT. The JDBC driver recognizes this restriction and takes the appropriate course of action.
  • A PreparedStatement must be used, meaning that there is no optimized support for Statement objects. The JDBC driver recognizes this restriction and takes the appropriate course of action.
  • The SQL statement must specify parameter markers for all the columns in the table. This means that you cannot either use constant values for a column or allow the database to insert default values for any of the columns. The JDBC driver does not have a mechanism to handle testing for specific parameter markers in your SQL statement. If you set the property to perform optimized blocked insertions and you do not avoid defaults or constants in your SQL statements, the values that end up in the database table are not correct.
  • The connection must be to the local system. This means that a connection using DRDA to access a remote system cannot be used because DRDA does not support a blocked insert operation. The JDBC driver does not have a mechanism to handle testing for a connection to a local system. If you set the property to perform an optimized blocked insertion and you attempt to connect to a remote system, the processing of the batch update fails.

This code example shows how to enable support for blocked insert processing. The only difference between this code and a version that does not use blocked insert support is use block insert=true that is added to the Connection URL.

Example: Blocked insert processing

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.
// Create a database connection
Connection c = DriverManager.getConnection("jdbc:db2:*local;use block insert=true");
BigDecimal bd = new BigDecimal("123456");

// Create a PreparedStatement to insert into a table with 4 columns
PreparedStatement ps =
  c.prepareStatement("insert into cujosql.xxx values(?, ?, ?, ?)");

// Start timing...
for (int i = 1; i <= 10000; i++) {
  ps.setInt(1, i);                       // Set all the parameters for a row
  ps.setBigDecimal(2, bd);
  ps.setBigDecimal(3, bd);
  ps.setBigDecimal(4, bd);
  ps.addBatch();                         //Add the parameters to the batch
}

// Process the batch
int[] counts = ps.executeBatch();

// End timing...

In similar test cases, a blocked insert is several times faster than performing the same operations when a blocked insert is not used. For example, the test performed on the previous code was nine time faster using blocked inserts. Cases that only use primitive types instead of objects can be up to sixteen times faster. In applications where there is a significant amount of work going on, change your expectations appropriately.