search cancel

FDM and DB2 masking error ERRORCODE=-4229, SQLSTATE=null

book

Article ID: 189750

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Hello, 

 

We are getting an error with masking some DB2 AIX with FDM 4.9.13

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

Release : 4.9

Component : CA Test Data Manager

Resolution


I did some quick checking and 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

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.

Resolving The Problem




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.