FDM and DB2 masking error ERRORCODE=-4229, SQLSTATE=null
Article ID: 189750
Updated On:
Products
Issue/Introduction
Getting an error with masking some DB2 AIX with FDM
In attachment you will have some log.
2020-04-20 15:51:31.163 Thread:TCPFO_ITRI - Starting at 2020.04.20 15:51:31.163 EDT
2020-04-20 15:51:31.179 Thread:TCPFO_ITRI Cursor Fetchsize:1000
2020-04-20 15:51:31.179 Thread:TCPFO_ITRI Autocommit:false
2020-04-20 15:51:47.523 [jcc][t4][102][10040][4.14.146] Incident de traitement par lots. Le lot a été soumis mais une exception s'est produite sur un membre individuel du lot.
Utilisez getNextException() pour obtenir les exceptions relatives aux éléments spécifiques de traitement par lots. ERRORCODE=-4229, SQLSTATE=null
2020-04-20 15:51:47.523 Thread:TCPFO_ITRI - Table causing error:TCPFO_ITRI
2020-04-20 15:51:47.523 Thread:TCPFO_ITRI - Select SQL:SELECT "NUM_FOLI","NUM_FOLI_ENCO","NUM_SEQU_CMPT","RESTART","ID_SEQ_EADM" FROM "EADM_A01794FMCDI"."TCPFO_ITRI" order by "ID_SEQ_EADM"
2020-04-20 15:51:47.523 Thread:TCPFO_ITRI - Update SQL:update "EADM_A01794FMCDI"."TCPFO_ITRI" SET "NUM_FOLI" = ?,"NUM_FOLI_ENCO" = ?,"NUM_SEQU_CMPT" = ?,"RESTART" = ? WHERE "ID_SEQ_EADM" = ?
2020-04-20 15:51:47.523 exit value = 1
Original
2020-04-20 15:51:47.523 [jcc][t4][102][10040][4.14.146] Incident de traitement par lots. Le lot a été soumis mais une exception s\'est produite sur un membre individuel du lot. Utilisez getNextExcept
Translation
2020-04-20 15:51:47.523 [jcc] [t4] [102] [10040] [4.14.146] Batch processing incident. The lot but an exception occurred on an individual member of the lot. Use getNextExcept
Environment
All supported FDM releases.
Cause
The error is caused by the DB2 Transaction log for the Data Warehouse becoming full and not able to process the full batch operation.
Found this article:
https://www.ibm.com/support/pages/db2-error-during-server-rename-batch-failure%C2%A0-batch-was-submitted-least-one-exception-occurred-individual-member-batch
DB2 error during server rename: Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch
Troubleshooting Problem
During a server rename getting an error in the log files, Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch, when using IBM Rational Team Concert (RTC).
Symptom
Errors reported in the log files during a server rename:
Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch.
The full stack trace of the error:
[com.ibm.team.repository.service.mapping.internal.ServerRenameService] ERROR sqlExceptionLogger
- SQL: UPDATE "RICALM"."REQUEST_HISTORY" SET "URL" = ?, "EXTERNAL_ID" = ? WHERE "
REQUEST_HISTORY_ID" = ?
SQL Exception #1
SQL Message: [jcc][t4][102][10040][4.14.88] 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
SQL State: null
Error Code: -4229
SQL Exception #2
SQL Message: Error for batch element #1: DB2 SQL Error: SQLCODE=-1476, SQLSTATE=40506, SQLERRMC=-964, DRIVER=4.14.88
SQL State: 40506
Error Code: -1476
Exception Details: Transaction rollback
SQL Exception #3
SQL Message: [jcc][103][10843][4.14.88] Non-recoverable chain-breaking exception occurred during batch processing. The batch is
terminated non-atomically. ERRORCODE=-4225, SQLSTATE=null
SQL State: null
Error Code: -4225
com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][4.14.88] 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
Resolution
Increase the LogFilSiz, LogPrimary, and LogSecond for the Data Warehouse.
Stop the IBM Collaborative Lifecycle Management (CLM) server and re-start it. This should start the processing of the batch process.
Changing the LogFilSiz, LogPrimary, and LogSecond variable is illustrated under the topic of Tuning transaction log characteristics in the IBM DB2 Information Center.
Also another Article:
*************************************************
https://www.ibm.com/support/pages/diagnosing-and-handling-error-code-4229
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.
This should help provide you with why this is happening. The ones that I checked all pointed to the Data Warehouse being full.
We did another way -Ddb2.jcc.traceLevel=-1 -Ddb2.jcc.traceDirectory=E:\\ARCHIVES\\LOGS
There was a trigger in the table that we didn't know.
Feedback
