Multi-row SQL operations in JDBC applications

The IBM® Data Server Driver for JDBC and SQLJ supports multi-row INSERT, UPDATE, and FETCH for connections to data sources that support these operations.

Multi-row INSERT

In JDBC applications, when you execute INSERT or MERGE statements that use parameter markers in a batch, if the data server supports multi-row INSERT, the IBM Data Server Driver for JDBC and SQLJ can transform the batch INSERT or MERGE operations into multi-row INSERT statements. Multi-row INSERT operations can provide better performance in the following ways:
  • For local applications, multi-row INSERTs result in fewer accesses of the data server.
  • For distributed applications, multi-row INSERTs result in fewer network operations.

You cannot execute a multi-row INSERT operation by including a multi-row INSERT statement in a statement string in your JDBC application.

Multi-row INSERT is used by default. You can use the Connection or DataSource property enableMultiRowInsertSupport to control whether multi-row INSERT is used. Multi-row INSERT cannot be used for INSERT FROM SELECT statements that are executed in a batch.

Multi-row FETCH

Multi-row FETCH can provide better performance than retrieving one row with each FETCH statement. For IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on Db2® for z/OS®, multi-row FETCH can be used for forward-only cursors and scrollable cursors. For IBM Data Server Driver for JDBC and SQLJ type 4 connectivity, multi-row FETCH can be used only for scrollable cursors.

When you retrieve data in your applications, the IBM Data Server Driver for JDBC and SQLJ determines whether to use multi-row FETCH, depending on several factors:

  • The setting of the enableRowsetSupport property
  • The setting of the useRowsetCursor property, for connections to Db2 for z/OS
  • The type of IBM Data Server Driver for JDBC and SQLJ connectivity that is being used
  • The version of the IBM Data Server Driver for JDBC and SQLJ

For IBM Data Server Driver for JDBC and SQLJ type 4 connectivity to Db2 for z/OS, one of the following sets of conditions must be true for multi-row FETCH to be used.

  • First set of conditions:
    • The IBM Data Server Driver for JDBC and SQLJ version is 3.51 or later.
    • The enableRowsetSupport property value is com.ibm.db2.jcc.DB2BaseDataSource.YES (1), or the enableRowsetSupport property value is com.ibm.db2.jcc.DB2BaseDataSource.NOT_SET (0) and the useRowsetCursor property value is true.
    • The FETCH operation uses a scrollable cursor.

      For forward-only cursors, fetching of multiple rows might occur through DRDA block FETCH. However, this behavior does not utilize the data source's multi-row FETCH capability.

  • Second set of conditions:
    • The IBM Data Server Driver for JDBC and SQLJ version is 3.1.
    • The useRowsetCursor property value is com.ibm.db2.jcc.DB2BaseDataSource.YES (1).
    • The FETCH operation uses a scrollable cursor.

      For forward-only cursors, fetching of multiple rows might occur through DRDA block FETCH. However, this behavior does not utilize the data source's multi-row FETCH capability.

For IBM Data Server Driver for JDBC and SQLJ type 2 connectivity to Db2 for z/OS the following conditions must be true for multi-row FETCH to be used.

  • The IBM Data Server Driver for JDBC and SQLJ version is 3.51 or later.
  • The enableRowsetSupport property value is com.ibm.db2.jcc.DB2BaseDataSource.YES (1).
  • The FETCH operation uses a scrollable cursor or a forward-only cursor.

For IBM Data Server Driver for JDBC and SQLJ type 2 connectivity on Db2 for z/OS, you can control the maximum size of a rowset for each statement by setting the maxRowsetSize property.

Multi-row positioned UPDATE or DELETE

The IBM Data Server Driver for JDBC and SQLJ supports a technique for performing positioned update or delete operations that follows the JDBC 1 standard. That technique involves using the ResultSet.getCursorName method to obtain the name of the cursor for the ResultSet, and defining a positioned UPDATE or positioned DELETE statement of the following form: 
UPDATE table SET col1=value1,…coln=valueN WHERE CURRENT OF cursorname
DELETE FROM table WHERE CURRENT OF cursorname

Multi-row UPDATE or DELETE when useRowsetCursor is set to true: If you use the JDBC 1 technique to update or delete data on a database server that supports multi-row FETCH, and multi-row FETCH is enabled through the useRowsetCursor property, the positioned UPDATE or DELETE statement might update or delete multiple rows, when you expect it to update or delete a single row. To avoid unexpected updates or deletes, you can take one of the following actions:

  • Use an updatable ResultSet to retrieve and update one row at a time, as shown in the previous example.
  • Set useRowsetCursor to false.

Multi-row UPDATE or DELETE when enableRowsetSupport is set to com.ibm.db2.jcc.DB2BaseDataSource.YES (1): The JDBC 1 technique for updating or deleting data is incompatible with multi-row FETCH that is enabled through the enableRowsetSupport property.

Recommendation: If your applications use the JDBC 1 technique, update them to use the JDBC 2.0 ResultSet.updateRow or ResultSet.deleteRow methods for positioned update or delete activity.