IBM Support

Diagnosing and handling error code -4229

Troubleshooting


Problem

Batch job runs and encounter errorcode -4229, but there is no message in db2diag.log for further diagnosis.

Symptom

Java batch job failure.

Cause

One of the SQLs to be executed by the Java batch has an error.

Environment

This affects any environments running Java program using the batch command.

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.

Resolving The Problem

To diagnose -4229, you can do one of the following :

- turn on JDBC trace
- set DIAGLEVEL to 4 ( be careful to prune db2diag.log periodically )
- use the getNextException() call to print out all the SQL exceptions.

This should be enough to tell you which SQL in the batch is giving problem.

[{"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Business Unit":{"code":"BU001","label":"Analytics Private Cloud"},"Component":"Programming Interface - JDBC","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5;11.1","Edition":"Advanced Workgroup Server;Enterprise Server"}]

Document Information

Modified date:
16 June 2018

UID

swg21995456