Nonstop Database, HiRDB Version 9 UAP Development Guide
In the JDBC 2.1 Core API, a batch update facility has been added to the Statement and PreparedStatement classes. This facility enables you to register multiple SQL statements or parameter values and execute them all at once.
When you execute a batch update, you can use facilities that use HiRDB arrays.
Facilities that use arrays are effective when you need to update quickly a large volume of data for HiRDB. For details about facilities that use arrays, see 4.8 Facilities using arrays.
The following notes apply to batch update with the Statement class.
Because the JDBC driver cannot execute multiple SQL statements simultaneously, it executes the registered SQL statements consecutively.
The following notes apply to batch update with the PreparedStatement class.
Note that the JDBC driver executes SQL statements consecutively in the following cases:
prepstmt.setInt(1,100); prepstmt.setInt(2,100); prepstmt.addBatch(); prepstmt.setInt(1,200); prepstmt.addBatch(); prepstmt.executeBatch();
The following notes apply to batch update with the CallableStatement class.
Because the JDBC driver cannot execute multiple rows of ? parameters for a stored procedure in the batch mode, it executes them consecutively.
If the SQL statements registered with addBatch contain one of the following SQL statements, you must use the batch update facility for SQL statements carefully, because the HiRDB server commits that SQL statement implicitly when the statement is executed:
When addBatch specifications for parameters and addBatch specifications for SQL statements are combined, the batch update facility executes the addBatch specifications sequentially instead of by batch update. An example is shown below:
PreparedStatement pstmt = con.prepareStatement("UPDATE T1 SET C1=? WHERE C2=?"); pstmt.setInt(1, 1); pstmt.setInt(2, 1); pstmt.addBatch(); pstmt.setInt(1, 2); pstmt.setInt(2, 2); pstmt.addBatch(); pstmt.addBatch("INSERT INTO T2 VALUES(1,2,3)"); pstmt.setInt(1, 3); pstmt.setInt(2, 4); pstmt.addBatch(); pstmt.setInt(1, 4); pstmt.setInt(2, 4); pstmt.addBatch(); pstmt.executeBatch(); |
When this UAP is executed, each addBatch unit becomes an SQL execution, because there are both addBatch specifications for parameters and addBatch specifications for SQL statements. Therefore, executing this UAP produces the same results as executing the following UAP:
PreparedStatement pstmt = con.prepareStatement("UPDATE T1 SET C1=? WHERE C2=?"); pstmt.setInt(1, 1); pstmt.setInt(2, 1); pstmt.executeUpdate(); pstmt.setInt(1, 2); pstmt.setInt(2, 2); pstmt.executeUpdate(); pstmt.executeUpdate("INSERT INTO T2 VALUES(1,2,3)"); pstmt.setInt(1, 3); pstmt.setInt(2, 4); pstmt.executeUpdate(); pstmt.setInt(1, 4); pstmt.setInt(2, 4); pstmt.executeUpdate(); |
When you use the batch update facility on a combination of addBatch for parameters and addBatch for SQL statements, it is recommended that you disable the auto-commit mode for the Connection class.
When batch update is executed with SQL statements that contain a ? parameter for HiRDB's BINARY type, sequential execution is executed instead of batch update when the following condition applies:
When batch update is executed with SQL statements that include a ? parameter for HiRDB's BLOB type, the statements are executed sequentially instead of by batch update.
The JDBC driver accumulates in the driver all parameters registered with the addBatch method until the executeBatch method is executed. You should make note of the amount of memory being used when you are registering multiple parameters.
When batch update is executed with a facility that uses HiRDB arrays, the maximum number of executions that the JDBC driver can request to the HiRDB server is 30,000. To register more than 30,000 parameters, you must divide them into groups of no more than 30,000 and request SQL execution to the HiRDB server for each group. Note also that because of the amount of memory in the JDBC driver that is used in this case, the performance enhancement expected for batch updating may not be realized. When more than 30,000 SQL executions are necessary, it is recommended that you execute the executeBatch method in units of 30,000 or fewer SQL executions.
The following table shows the update count (int-type array) that is reported in the return value of the getUpdateCounts method of BatchUpdateException that occurs during batch update processing.
Table 18-56 Update count reported in the return value of the getUpdateCounts method
Batch update execution mode | BATCHEXCEPTION_BEHAVIOR#1 setting | |
---|---|---|
TRUE | FALSE | |
Batch execution using the array facility | Array containing no elements | Array containing n elements n: The addBatch that registered the parameter resulting in the error. When an error occurs, all elements are set to Statement.EXECUTE_FAILED because processing is rolled back. |
Consecutive execution using a JDBC driver | Array containing as many elements as there are SQL statements executed The number of updated rows#2 is set in each array element. |
Array containing n elements n: The addBatch that registered the parameter or SQL statement resulting in the error. Array elements are set to the following values: Elements 0 to n - 2: Number of updated rows#2 Element n - 1: Statement.EXECUTE_FAILED |
The following are examples of update count:
Example program of batch execution using the array facility
PreparedStatement pstmt = con.prepareStatement("INSERT INTO T1 VALUES(?,?)"); pstmt.setInt(1, 1); pstmt.setString(2,"aaaa"); pstmt.addBatch(); pstmt.setInt(1, 2); pstmt.setString(2,"bbbbbbbb"); pstmt.addBatch();.......................................[A] pstmt.setInt(1, 3); pstmt.setString(2,"cccc"); pstmt.addBatch(); pstmt.executeBatch(); |
Example program of consecutive execution using a JDBC driver
Statement stmt = con.createStatement(); stmt.addBatch("INSERT INTO T1 VALUES(1,'aaaa')"); stmt.addBatch("INSERT INTO T1 VALUES(2,'bbbbbbbb')");...[A] stmt.addBatch("INSERT INTO T1 VALUES(3,'cccc')"); stmt.executeBatch(); |
The following table shows the update count that is returned by the getUpdateCounts method when these example programs are executed and the parameter or SQL statement registered in [A] results in an error:
Batch update execution mode | BATCHEXCEPTION_BEHAVIOR setting | |
---|---|---|
TRUE | FALSE | |
Batch execution using the array facility | int-type array containing no elements | int-type array containing 2 elements Value of element 0: Statement.EXECUTE_FAILED Value of element 1: Statement.EXECUTE_FAILED |
Consecutive execution using a JDBC driver | int-type array containing 1 element Value of element 0: Number of updated rows |
int-type array containing 2 elements Value of element 0: Number of updated rows Value of element 1: Statement.EXECUTE_FAILED |
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.