Workpoint Database Cleanup Procedure - Identity Manager

Products

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

Issue/Introduction

This article outlines the operational procedure for the systematic purge and maintenance of the Workpoint runtime database within CA Identity Manager and Identity Suite. As workflow-intensive environments scale, the administrative removal of legacy, completed, and canceled job data becomes essential to ensure the continued stability and responsiveness of the workflow engine.

This technical guide provides Database Administrators (DBAs) and System Engineers with the SQL commands and service management protocols required to optimize database performance, prevent application-server resource exhaustion, and maintain efficient processing of workflows and tasks.

 

Environment

Identity Manager 14.5

Cause

The Workpoint workflow engine maintains a persistent record of every process instance (WP_PROCI) and its associated task history to ensure auditability and state recovery. Over time, the accumulation of high-volume transaction data leads to several critical issues:

• Engine Latency: A bloated WP_PROCI table increases the overhead for the workflow engine when querying active process states, leading to "In Progress" tasks sticking or timing out.
• JMS Queue Congestion: When the runtime database reaches a certain threshold, the overhead of managing stale data can cause log flooding in the JMS queues, potentially crashing the application server nodes.
• Database Contention: Large data sets increase index fragmentation and I/O wait times during standard Identity Manager operations, directly impacting Identity Portal responsiveness and user login times.
• Resource Exhaustion: Excessive table growth consumes storage and extends the duration of mandatory maintenance tasks, such as backups and index rebuilds.

Resolution

I. Prerequisites & Preparation

1. Full Database Backup: Ensure a verified backup of the Workpoint database exists.
2. Environment Validation: Test all SQL scripts and procedures in a non-production/staging environment before execution in production.
3. Product Scope: This procedure applies to CA Identity Manager, CA Identity Governance, and CA Identity Suite (including Identity Portal).

II. Service Management (Critical Safety)

To prevent data corruption, record locking, or JMS queue log flooding, follow these shutdown steps:

1. Shut Down Application Nodes: Completely stop all application server services (JBoss, WebSphere, or WebLogic) connected to the Workpoint database.
2. Verify Node Status: Ensure no active workflow engine nodes are processing requests during the maintenance window.

III. Flagging Jobs for Deletion

Execute the following SQL command to identify and mark jobs for removal. The WHERE clause should be adjusted to target your specific date range or job states (State 4 = Canceled, State 5 = Completed).

sql

 

UPDATE WP_PROCI SET LU_ID = 'Delete Job',     ROW_VERSION = ROW_VERSION + 1,     LU_DATE = getdate() WHERE INITIATION_DATE > {ts 'YYYY-MM-DD 00:00:00'}   AND INITIATION_DATE < {ts 'YYYY-MM-DD 23:59:59'}   AND (PROC_STATE_ID = 4 OR PROC_STATE_ID = 5)

IV. Execution & Verification

1. Run Purge Procedure: Execute the stored procedure spWP_DELETE_JOBS. This will permanently remove all records marked with LU_ID = 'Delete Job'.
2. Database Optimization: Perform a standard database shrink or index rebuild (per your DBA policy) to reclaim disk space.
3. Service Restoration: Restart the application server services and monitor the logs to ensure the workflow engine initializes correctly without errors.

Note: The WHERE clause 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.