Work Item Removal from Task Persistence Database

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Identity Suite

Issue/Introduction

Task items within the persistence database may remain in an incomplete state due to system interruptions or process failures. Administrators may need to remove these abandoned work items to clean the database. This article provides the required SQL statements to delete uncompleted work items while maintaining database referential integrity.

Environment

Identity Manager

Resolution

Prerequisites and Warnings

• Backup Required: Always perform a full database backup before executing any deletion queries to prevent data loss.
• Scope: These statements are specific to Microsoft SQL Server. Ensure the target database is correctly identified before execution.
• Order of Operations: Execute the following SQL statements in the exact order provided to avoid foreign key constraint conflicts.

Deletion Procedure

Run the following SQL statements in the provided order:

Possible foreign keys can prevent us to simply delete WP_WORK_ITEM rows. 

The referential path is the following: 

WP_WORK_ITEM <--(fk1_wp_wi_alert) <-- WP_WI_ALERT <-- (fk1_wp_wi_alert_act) <-- WP_WI_ALERT_ACT 

 

To avoid this referential constraint conflict run the 3 following statements in this order: 

 

DELETE child2 FROM [WP_WI_ALERT_ACT] AS child2 

INNER JOIN [WP_WI_ALERT] AS child1 ON child2.WI_ALERT_ID = child1.WI_ALERT_ID AND 

child2.WI_ALERT_DB=child1.WI_ALERT_DB 

INNER JOIN [WP_WORK_ITEM] AS parent ON child1.ACTI_ID = parent.ACTI_ID AND 

child1.ACTI_DB = parent.ACTI_DB AND 

child1.WI_ITERATION=parent.WI_ITERATION 

WHERE parent.COMPLETE_DATE is null; 

 

DELETE child FROM [WP_WI_ALERT] AS child 

INNER JOIN [WP_WORK_ITEM] AS parent ON child.ACTI_ID = parent.ACTI_ID AND 

child.ACTI_DB = parent.ACTI_DB AND 

child.WI_ITERATION=parent.WI_ITERATION 

WHERE parent.COMPLETE_DATE is null; 

 

DELETE FROM [WP_WORK_ITEM] WHERE COMPLETE_DATE is null;