Clarity PPM: Time Slicing job fails due to duplicate records being inserted

Document ID : KB000023178
Last Modified Date : 01/06/2018
Show Technical Document Details
Issue:

The Time Slicing job is failing. We have tried to restart BG services but the same repeated error  can be seen in the BG logs:

MSSQL ERROR:

    Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]Cannot insert duplicate key row in object 'dbo.PRJ_BLB_SLICES' with unique index 'PRJ_BLB_SLICES_U2'.
    SQL error code: 3621
    Error message: [CA Clarity][SQLServer JDBC Driver][SQLServer]The statement has been terminated.
    ....
    Using input:
    {sliceDate=2015-02-01T00:00:00, sliceID=11438446, role_id=5005473, startDate=2015-02-01T00:00:00, objectID=5003935, resource_id=5005473, unit=0, finishDate=2015-03-01T00:00:00, requestID=6, slice=159.6, investment_id=5006074}

ORACLE ERROR:

    ERROR message: niku.blobcrack (none:none:none) Exception during blobcrack process
    com.niku.union.persistence.PersistenceException:
    SQL error code: 1
    Error message: ORA-00001: unique constraint (CLARITY.PRJ_BLB_SLICES_U2) violated
    ....
    Using input:
    {sliceDate=2015-02-01T00:00:00, sliceID=11438446, role_id=5005473, startDate=2015-02-01T00:00:00, objectID=5003935, resource_id=5005473, unit=0, finishDate=2015-03-01T00:00:00, requestID=6, slice=159.6, investment_id=5006074}
 

Cause:

The constraint error occurs because the Time Slicing job is try ng to add a record that is already there.
The table/index can be different from that of what appears in the BG logs.

This issue can occur if contention appears in the database and the database session is not handled.

Resolution:

Stabilize the job scheduler:
a. Set all jobs that are WAITING to PAUSED;

b. Set all jobs that are SCHEDULED to PAUSED;

c. Make note of the PROCESSING jobs and its scheduled times as they will need to be re-entered later.

d. Set all jobs that are in PROCESSING to CANCELLED

e. Delete all CANCELLED job instances

f. Restart all BG services.

g. Run an immediate instance of the Time Slicing job to check if the same error occurs.

If the same error occurs, please contact the Support team for further assistance.
The BG logs will need to be obtained and reviewed to obtain the slice data table and its corresponding records necessary to construct SQL backup and delete statements to resolve the issue.

Additional Information:

For root cause for deadlocking, please have the DBA team review.

MSSQL - https://support.microsoft.com/en-us/help/224453/inf-understanding-and-resolving-sql-server-blocking-problems