How do I clear unwanted Workpoint jobs from the runtime database?

Products

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

Issue/Introduction

The Workpoint database schema is created during the IM install process, together with all other IM databases and, by default, part of the same DB object store instance / SID.

The table called WP_PROCI is the only table that contains actual jobs (i.e. Work Items). These are the objects we see when we log into the IME (Identity Manager Environment) and select View My Worklist task.

 

The spWP_DELETE_JOBS is a stored procedure which comes with the installation. 

For SQL DBs you can find it under your IM DB instance -> Programmability.

Environment

Identity Manager 14.x

Resolution

Key points to consider before taking any action with this stored procedure (SP):

1. The SP will run against multiple table in the workpoint DB. You can view the list of tables from its nested stored procedure spWP_DELETE_JOBS_BYLEVEL, which also comes with the installation. The WP_PROCI table specifically contains the main handle for a job/worklist object used at runtime. It is where IM pulls the worklist items from. These items are connected to records in the tasksession12_5 table (i.e. parent and approval tasks). Therefore we should be mindful of what we decide to remove.
2. In the WP_PROCI table there's a column called PROC_STATE_ID. This is mapped to the table WP_PROC_STATE which lists the states:

PROC_STATE_ID     NAME

1        Planned
2        Active
3        Error
4        Canceled
5        Complete
6        Suspended
7        Created

3. In the WP_PROCI table there's also column called LU_ID. In order for spWP_DELETE_JOBS to clear jobs, the LU_ID value of that record should be set to Delete Job. Please make sure the PROC_STATE_ID of the records that you want deleted are in an end state and not active.
4. BEFORE setting the LU_ID to Delete Job on the unwanted records in the WP_PROCI table and running the SP, you will need to shutdown all application server nodes. If you don't, the application server will continue to load the transactions associated with these worklist items from the JMS queue and flood the log file with retries indefinitely. The retries will always fail because the associated worklist item no longer exists. A restart thereafter will not resolve the problem, only a clearing of the JMS queue will resolve this. So please have all nodes shutdown before running it. 

Additional Information

Below are the commands – in order – needed to run for deleting jobs from workpoint DB. In this example we will remove a job with PROCI_ID = 2077:

update [imdb].[dbo].[WP_PROCI] set proc_state_id = 4 where proci_id = 2077

update [imdb].[dbo].[WP_PROCI] set lu_id = 'Delete Job' where proci_id = 2077

execute spWP_DELETE_JOBS