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


Article ID: 23178


Updated On:


Clarity PPM On Premise


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. Here are examples:


    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}


Caused by: java.sql.SQLIntegrityConstraintViolationException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (CLARITY.PRJ_BLB_SLICES_W_AVL_U1) violated 
ORA-06512: at line 1 
ORA-06512: at "CLARITY.PRJ_BLB_SLICE_MERGE_SP", line 128 
ORA-06512: at line 1

The table name and constraint can vary, depending on the data being inserted. Examples include:



The constraint error occurs because the Time Slicing job is attempting to add a record that is already there.
This issue can occur if contention appears in the database and the database session is not handled.

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.




Skip the optional steps and only perform Steps C and E if you do not have a Job Scheduler issue.

Step A (Optional). Stabilize the job scheduler. 

1. Set all jobs that are WAITING to PAUSED;

2. Set all jobs that are SCHEDULED to PAUSED;

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

4. Set all jobs that are in PROCESSING to CANCELLED.

5. Delete all CANCELLED job instances.

If there are Cancelled jobs that cannot be deleted as it is related to processes, then these can be ignored.

Step B (Optional). Stop all BG services.

Step C.  Have DBA team delete and regenerate the slice data on the table.

1. Review the BG logs to obtain the table name.

2. Run the following queries to validate the slice id and table name.

a. select id, request_name, table_name from PRJ_BLB_SLICEREQUESTS where table_name = '[TABLE_IN_BG_LOG]'

Check how many records needs to be deleted, in order to regenerate the data.
NOTE: Actual data will be regenerated, therefore no data is being deleted.

select slice_request_id, count(*) from
where slice_request_id in
group by slice_request_id

c. Make a backup of the table with the slices.

delete from [TABLE_NAME]
where slice_request_id in (x);

e. Reset the dates to force a reslice

set expiration_date = null, 
request_completed_date = null 
where id in

Step D (Optional). Restart all BG services. (Restart is not usually needed unless there was an issue with the Job scheduler)

Step E. 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.