How to identify and delete custom orphan slices

Document ID : KB000024374
Last Modified Date : 14/02/2018
Show Technical Document Details

Summary:

When a custom time slice request is deleted from the application, records from the PRJ_BLB_SLICEREQUESTS table (table where slice requests are stored) are deleted but time slice data in PRJ_BLB_SLICES table (table where slices are stored) are not deleted. This results in 'orphan' slice records.  

Instructions:

For SaaS / On Demand Customers:  Contact CA Technical Support for review and resolution.  

For On Premise Customers:

The following resolution is pre-approved by our development team for removing 'orphaned' time slice records caused by the removal of a user-defined Time Slice Definition.

Note: CA recommends to backup the database before deleting any record.

Also note that if you have many orphan records for example millions, a delete of these records can take a considerable amount of time. You would also want to stop the background server before running the delete. This will stop any processing of time slice records which would certainly slow the delete down.

The following query checks for orphan slices.

The query will show the slice request with the orphan slices and the number of orphan slices present

  • Identify the custom orphan time slices

    SELECT s.slice_request_id, count(*)
    FROM prj_blb_slices s
    WHERE NOT EXISTS (SELECT 1 FROM prj_blb_slicerequests r WHERE r.id =
    s.slice_request_id)
    GROUP BY s.slice_request_id

  • Delete the custom orphan time slices

    DELETE FROM prj_blb_slices
    WHERE NOT EXISTS
    (SELECT 1 FROM prj_blb_slicerequests r
    WHERE r.id = prj_blb_slices.slice_request_id) ;

   commit ;