Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

IntegrityConstraintViolation with Obj-Save (Pega 7.1.8 upgrade)

SA-14335

Summary


 
An application that has been upgraded from Pega 7.1.5 to Pega 7.1.8 throws an IntegrityConstraintViolationException during Obj-Save in an activity.
The application's data model has a cascading primary key > foreign key relationship.
The application is using the IBM DB2 zOS database on server version 10.1.5.


Error Messages


 
Error in commit()
com.pega.pegarules.pub.database.IntegrityConstraintViolationException: [jcc][t4][102][10040][4.15.82] 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
From: (A9F921C01C83BF05414A35741997D2DA0:MachineHealth)
SQL: MERGE INTO ADVCBMP.SA_SUPPORT_PLAN_EVENT t USING (VALUES (? , ? , ? , ? , ? , ? , ? , ? , ?)) AS inputs ("SUPPORT_PLAN_EVENT_ID" , "CURRENT_COUNT" , "END_DATE" , "EVENT_ID" , "MAX_COUNT" , "OFFSET_COUNT" , "RENEWAL_DATE" , "START_DATE" , "TRIGGER_TS") ON (t.SUPPORT_PLAN_EVENT_ID = inputs."SUPPORT_PLAN_EVENT_ID") WHEN MATCHED THEN UPDATE SET CURRENT_COUNT = inputs."CURRENT_COUNT" , END_DATE = inputs."END_DATE" , EVENT_ID = inputs."EVENT_ID" , MAX_COUNT = inputs."MAX_COUNT" , OFFSET_COUNT = inputs."OFFSET_COUNT" , RENEWAL_DATE = inputs."RENEWAL_DATE" , START_DATE = inputs."START_DATE" , TRIGGER_TS = inputs."TRIGGER_TS" WHEN NOT MATCHED THEN INSERT (SUPPORT_PLAN_EVENT_ID , CURRENT_COUNT , END_DATE , EVENT_ID , MAX_COUNT , OFFSET_COUNT , RENEWAL_DATE , START_DATE , TRIGGER_TS) VALUES (inputs."SUPPORT_PLAN_EVENT_ID" , inputs."CURRENT_COUNT" , inputs."END_DATE" , inputs."EVENT_ID" , inputs."MAX_COUNT" , inputs."OFFSET_COUNT" , inputs."RENEWAL_DATE" , inputs."START_DATE" , inputs."TRIGGER_TS")
 
Caused by SQL Problems.
Problem #1, SQLState null, Error code -4229: com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][4.15.82] 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
Problem #2, SQLState 23503, Error code -530: com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: THE INSERT OR UPDATE VALUE OF FOREIGN KEY SPEVEVIN IS INVALID. SQLCODE=-530, SQLSTATE=23503, DRIVER=4.15.82
Problem #3, SQLState 23503, Error code -530: com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #1: THE INSERT OR UPDATE VALUE OF FOREIGN KEY SPEVEVIN IS INVALID
at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:296)

 
 

Steps to Reproduce


 
  1. Create an activity that uses Obj-Save.
  2. Make sure that the table has a cascading primary key-foreign key relationship.
  3. Run the flow.
 
 

Root Cause


 
A defect in Pegasystems’ code or rules

Pega 7.1.7 introduced a new API that results in non-deterministic order of database SQL statement execution within a transaction.
 
In this case, the instance that is the target of the foreign key constraint must be inserted before the instance that has the primary key constraint. Because of the non-deterministic order of database SQL statement execution, this is not always the case, even if the  Save operations are performed in the desired order.

 

Resolution


 
The issue was fixed in Pega 7.1.9.
Find the release note for the Resolved Issue, Issue 212424, Modified ClassName pass method for external DBs.

Best Practice: Update or upgrade to the latest Pega Platform release.

If you cannot update or upgrade your Pega deployment, perform the following local-change:
  1. Save the prpc_j2ee14_ws.ear file to a temp directory.
  2. Locate the prconfig.xml file in the following directory path:
    <PRPC Home Direcotry>\archives\prpc_j2ee14_ws.ear\APP-INF\lib\prresources.jar\prconfig.xml
  3. Add the following prconfig setting: 
    <env name=”database/batchUpdates”  value=”-1”/>
  4. Repackage the files prresources.jar and prpc_j2ee14_ws.ear.
    Note: Be careful with the Manifest.mf file while repackaging the EAR and JAR files. If it gets corrupted, the application might not start.
  5. Restart all nodes by clearing PegaRules_Extract_Marker.txt file from PegaRules directory.

Published February 7, 2019 - Updated October 8, 2020

Was this useful?

50% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us