Workpoint Database Cleanup Procedure - Identity Manager

Products

• CA Identity Manager
• CA Identity Suite

Issue/Introduction

This article provides the necessary steps to perform a database cleanup for Workpoint workflows in Identity Suite. This procedure is intended for database administrators managing workflow performance or clearing legacy job data.

Environment

Identity Manager 14.5

Cause

Large volumes of completed or cancelled Workpoint jobs can lead to database bloat and performance degradation in the workflow engine.

Resolution

Perform the following steps to safely purge legacy job data.

1. Verify Backup: Ensure a full backup of the Workpoint database is completed. Test all scripts against a non-production (extraneous) backup database before running them in a live environment.
2. Stop Services: Completely shut down all application server services connected to the Workpoint database to prevent data locks or corruption.
3. Identify Jobs for Deletion: Execute SQL to mark specific jobs. The following example marks jobs in a Canceled (4) or Complete (5) state within a specific date range:
   • UPDATE WP_PROCI SET LU_ID = 'Delete Job', ROW_VERSION = ROW_VERSION + 1, LU_DATE = getdate() WHERE INITIATION_DATE > {ts '2010-01-01 00:00:00'} AND INITIATION_DATE < {ts '2010-03-31 23:59:59'} AND (PROC_STATE_ID = 4 OR PROC_STATE_ID = 5)
4. Execute Deletion Procedure: Execute the stored procedure spWP_DELETE_JOBS. This marks all jobs identified in Step 3 for permanent removal.
5. Verify and Restart: Verify that the database size has been optimized. Once complete, restart the application server services.

Note: The WHERE clause in Step 3 can be adjusted to filter by specific date ranges or other attributes depending on your business requirements.

Additional Information

To speak with a customer representative or a Support Engineer see Contact Support. Scroll to the bottom of the page and click on your respective region.