Pruning of the Case Analyzer database fails with the following error: Prune events operation failed for store CADB

Technote (troubleshooting)

Problem(Abstract)

Pruning of the IBM Case Foundation Case Analyzer database fails with the following error: Prune events operation failed for store CADB. Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "D_DMWorkItem_F_DMWIP_FK1". The conflict occurred in database "CADB", table "dbo.F_DMWIP".

Symptom

When attempting to prune the Case Analyzer database the following error is reported in the pesvr_system.log file on the CPE server:

2015/09/10 02:20:01.272-0700 RPCHandler 145d7bf5 [Info] Case Analyzer -Started prune events operation for store CADB
2015/09/10 02:20:04.986-0700 RPCHandler 145d7bf5 [Error] Case Analyzer - Prune events operation failed for store CADB. Exception: com.microsoft.
sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "D_DMWorkItem_F_DMWIP_FK1". The conflict occurred in database "CADB", table "dbo.F_DMWIP".
at com.microsoft.sqlserver.jdbc.SQLServerException.
makeFromDatabaseError(SQLServerException.java:197)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult
(SQLServerStatement.java:1493)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.processResults
(SQLServerStatement.java:1135)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
getOutParameter(SQLServerCallableStatement.java:112)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
getterGetParam(SQLServerCallableStatement.java:387)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getValue
(SQLServerCallableStatement.java:393)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getInt
(SQLServerCallableStatement.java:437)
at com.ibm.ws.rsadapter.jdbc.WSJdbcCallableStatement.getInt
(WSJdbcCallableStatement.java:480)
at filenet.eventexporter.ca.sql.PADataEnvironmentSQLServer.
pa_X_TerminatedWorkflows_Prune(PADataEnvironmentSQLServer.java:274)
at filenet.eventexporter.ca.main.PAPruneEvents.run(PAPruneEvents.java:
167)
at java.lang.Thread.run(Thread.java:780)
; Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The
DELETE statement conflicted with the REFERENCE constraint
"D_DMWorkItem_F_DMWIP_FK1". The conflict occurred in database
"CADB", table "dbo.F_DMWIP".
at com.microsoft.sqlserver.jdbc.SQLServerException.
makeFromDatabaseError(SQLServerException.java:197)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult
(SQLServerStatement.java:1493)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.processResults
(SQLServerStatement.java:1135)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
getOutParameter(SQLServerCallableStatement.java:112)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.
getterGetParam(SQLServerCallableStatement.java:387)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getValue
(SQLServerCallableStatement.java:393)
at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getInt
(SQLServerCallableStatement.java:437)
at com.ibm.ws.rsadapter.jdbc.WSJdbcCallableStatement.getInt
(WSJdbcCallableStatement.java:480)
at filenet.eventexporter.ca.sql.PADataEnvironmentSQLServer.
pa_X_TerminatedWorkflows_Prune(PADataEnvironmentSQLServer.java:274)
at filenet.eventexporter.ca.main.PAPruneEvents.run(PAPruneEvents.java:
167)
at java.lang.Thread.run(Thread.java:780)

Resolving the problem

Before following the technote to clean up the orphan records in the WIP tables, please review the records in the WIP tables and confirm that they are orphaned.

Run the below two queries:

SELECT *  from F_DMWIP where Workflow_key in (select Workflow_key from X_TerminatedWorkflows);
SELECT * from F_DMWorkflowWIP where Workflow_key in (select Workflow_key from X_TerminatedWorkflows);

Review the records returned by the above query. If you confirm they are orphaned (meaning you can see your system and say these are in fact completed workflows and workitems), then follow the technote to workaround the issue.
To resolve the problem execute the following steps and script on the CADB:

1. Backup the CADB DM + OLAP databases

2. Run the following script from Microsoft SQL Management Studio Query screen:

Use CADB;
delete from F_DMWIP where Workflow_key in (select Workflow_key from X_TerminatedWorkflows);
delete from F_DMWorkflowWIP where Workflow_key in (select Workflow_key from X_TerminatedWorkflows);

3. Re-run the Case Analyzer database pruning job from CAPTM.