Diagnosing and handling error code -4229

Diagnosing The Problem

There are some technical notes on sqlcode -4429 , and these are very specific to various product. Such as :

http://www-01.ibm.com/support/docview.wss?uid=swg1IC84077
http://www-01.ibm.com/support/docview.wss?uid=swg1IV61614
http://www-01.ibm.com/support/docview.wss?uid=swg1JR49638
http://www-01.ibm.com/support/docview.wss?uid=swg1IT07006
http://www-01.ibm.com/support/docview.wss?uid=swg1IV72113

This article however, seek to explain that -4229 is a generic error returned by Java when the 'batch' facility is used, and encounters an error. A 'batch' is a series of SQLs to be executed by Java. You will get -4229 if any of the SQL encounters an error. This problem can be easily demonstrated with a very short Java program :

Step 1 : create a table tab1 ( f1 not null primary key, f2 char(30) )

Step 2 : Java program

public static void main(String[] args) throws SQLException, ClassNotFoundException {
Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection con = DriverManager.getConnection(url,user,pw) ;

PreparedStatement pstmt = con.prepareStatement("insert into tab1 values ( ? , ? )");
for ( int i = 1 ; i < 5 ; i++ ) {
pstmt.setInt(1,i);
pstmt.setString(2, "string " + i );
pstmt.addBatch();
}
pstmt.setInt(1,4);
pstmt.setString(2, "string " + 4 );
pstmt.addBatch();
pstmt.executeBatch() ;
}

In this sample program, it inserts the rows as a batch.

( 1 , "string 1" ) ,
( 2 , "string 2" ) ,
( 4 , "string 4" ) ,
( 4 , "string 4" ) , <== failure

The last insert would cause batch failure because that would violate the primary key. Depending on your autocommit setting, you would insert 4 rows or non at all.

The above scenario will generate the following Java exception message :

Exception in thread "main" com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][3.65.77] 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. ERRORCODE=-4229, SQLSTATE=null

The message does not give you any hint what went wrong. Neither will you see any messages in db2diag.log, unless you set DIAGLEVEL to 4. At level 4 - you would see the reason for batch failure.
Example :

2016-12-07-11.37.47.732514+660 I12437E617 LEVEL: Info
PID : 31159 TID : 140038151071488 PROC : db2sysc 0
....
MESSAGE : ZRC=0x80090005=-2146893819=SQLI_DUPKEY "Duplicate key
violation" DIA8005C Duplicate key encountered, file token "" index ...

2016-12-07-11.37.47.732883+660 I13929E873 LEVEL: Info
PID : 31159 TID : 140038151071488 PROC : db2sysc 0
INSTANCE: v105_07 NODE : 000 DB : SAMPLE
....
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -803 sqlerrml: 14

In this example, the last INSERT encountered -803 - duplicate value violation.