Batch heterogeneous update exception handling

If an error occurs while performing a heterogeneous batch update, a com.ibm.pdq.runtime.exception.UpdateManyException is thrown. You can retrieve information about the exceptions and information about the SQL statements that generate the exceptions. A com.ibm.pdq.runtime.exception.HeteroBatchSQLException contains the SQL error information.

You can retrieve a two-dimensional int array that contains the update count for the batch heterogeneous update by calling UpdateManyException.getUpdateCounts() method. The array contains the update counts as would be returned if the batch completed successfully.

The values in the array are either update counts or a Java™ EXECUTE_FAILED. You can use the information in the array to find where the errors occurred.

You can retrieve the exceptions in the follow ways:

Example

The following heterogeneous batch example performs six updates. The following occurs when you run the batch:
  1. Batch update with the updateMany() method performs three inserts into Table A.
  2. A single UPDATE SQL statement that modifies five rows of Table F.
  3. A single INSERT SQL statement into Table B that fails with an SqlIntegrityConstraintViolationException since it tries to insert an existing Primary Key.
  4. A single DELETE SQL statement that deletes five rows from Table G.
  5. A batch update with the updateMany() method that performs of two inserts into Table H. Both inserts fail with a SqlIntegrityConstraintViolationException since they try to insert an existing Primary Key.
  6. A single INSERT SQL into Table I.
An UpdateManyException is thrown:

com.ibm.pdq.runtime.exception.UpdateManyException: [pdq][10363][3.200.401] One or more exceptions occurred while pureQuery™ ran the batch of heterogenous SQL statements.

You call the method getHeterogeneousUpdateCounts () to return a two dimensional int array of update counts to determine the failed SQL statements. The following table lists the array contents:

Table 1. Table containing heterogeneous batch update counts
Array index Count1
[0][0] 1
[0][1] 1
[0][2] 1
[1][0] 5
[2][0] EXECUTE_FAILED
[3][0] 5
[4][0] EXECUTE_FAILED
[4][1] EXECUTE_FAILED
[5][0] 1
Note:
  1. EXECUTE_FAILED is the JDBC-defined constant java.sql.Statement.EXECUTE_FAILED.
You can retrieve the exceptions with the following methods:
  • UpdateManyException.getHeteroBatchSQLExceptionArray () returns an array that contains row specific HeteroBatchSQLExceptions. If there are multiple exceptions for a row, the exceptions are chained together.
  • UpdateManyException.getBatchNonSpecificExceptions(), that returns non-row specific HeteroBatchSQLExceptions.

The following table lists the array returned by UpdateManyException.getHeteroBatchSQLExceptionArray():

Table 2. Array containing row specific HeteroBatchSQLExceptions
Array index Value
[0][0] null
[0][1] null
[0][2] null
[1][0] null
[2][0] HeteroBatchSQLException that contains the following information:

com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #1: in stmt #3: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.G" from having duplicate values for the index key.

[3][0] null
[4][0] HeteroBatchSQLException that contains the following information:

com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #1: in stmt #5: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.H" from having duplicate values for the index key.

[4][1] HeteroBatchSQLException that contains the following information:

com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #2: in stmt #5: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.H" from having duplicate values for the index key.

[5][0] null

UpdateManyException.getBatchNonSpecificExceptions() returns a null since there are no non-row specific SQLExceptions.

You can call the UpdateManyException.getCause() method and UpdateManyException.getNextException() method to retrieve the HeteroBatchSQLExceptions that are chained together. The following list shows the HeteroBatchSQLExceptions that are returned by calling getCause() and calling getNextException().

  1. getCause() call returns the cause:

    com.ibm.db2.jcc.am.BatchUpdateException: [jcc][102][10040][3.63.46] Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. Use getNextException() to retrieve the exceptions for specific batched elements.

  2. The first getNextException() call returns a HeteroBatchSQLException that contains the following information:

    com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #1: in stmt #3: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.G" from having duplicate values for the index key.

  3. The second getNextException() call returns a HeteroBatchSQLException that contains the following information:

    com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #1: in stmt #5: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.H" from having duplicate values for the index key.

  4. The third getNextException() call returns a HeteroBatchSQLException that contains the following information:

    com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #2: in stmt #5: One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "DB2ADMIN.H" from having duplicate values for the index key.

  5. The fourth getNextException() call returns:

    null


Feedback