Cleaning up Task Persistence DB of unnecessary data
search cancel

Cleaning up Task Persistence DB of unnecessary data


Article ID: 46396


Updated On:


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



This article helps you identify and clean up ONLY the stale or unnecessary data in your runtime Task Persistence database. This article does not deal with copying the data into Archive database first (that is, backing it up): continue reading only if your data is cleared to be removed.

Important note: Please take a full Task Persistence database backup prior to performing these steps. Failing to do so might corrupt your runtime data and your system will not be able to function.




Identity Manager-14.x

This article is equally relevant to both Oracle and SQL databases when used for Task Persistence.


You might use this article in the following circumstances:

1. When you have Identity Manager Environments (IMEs) that were previously created and are no longer needed (or have been previously deleted from the IM Management Console)

2. When you want to get rid of a certain task that doesn't need archiving 

3. When you have a large amount of transactions to remove and the Cleanup Submitted Tasks functionality (in the IM UI) is not scaling


First you will need to identify the transactions you want removed from Task Persistence. Shown below are a few examples:


a) You can find your IME OID from the IM Management Console: http://app_server>:<port>/iam/immanage --> Environments --> your IME --> OID field

b) The following SELECT statement originates from the garbageCollectTaskPersistence stored procedure created in your IM database during the CA Identity Manager installation. After you are done manipulating the SELECT statement to fetch the transactions you want removed, you create a new stored procedure as a copy of garbageCollectTaskPersistence, and then replace your SELECT statement with the existing one. No need to make any other changes. 

--- Example 1: All tasks (In Progress or else) belonging to an IME that is not used anymore or previously deleted from IM Management Console.

            SELECT * FROM tasksession12_5 WHERE environmentid = ‘1’; --  enter your IME OID instead of 1

--- Example 2: A specific task that happens very frequently and does not need to be backed up into Archive db. Age of task does not matter.

            SELECT * FROM tasksession12_5 WHERE environmentid = ‘1’ --  enter your IME OID instead of 1

                     AND name = ‘Provisioning Delete User’;

--- Example 3: Tasks that reached an end state, older than 1/1/2015. And, tasks that are in Audited state that are older than 30 days, only for IME 1.

            SELECT * FROM tasksession12_5 WHERE environmentid = '1' --  enter your IME OID instead of 1
                       AND (
                                                 (tasksession12_5.state = 512) --   mark for deletion 
                                            OR (tasksession12_5.state = 128) --  completed  
                                            OR (tasksession12_5.state = 256) --  cancelled   
                                            OR (tasksession12_5.state = 32) -- rejected   
                                            OR (tasksession12_5.state = 2) -- invalid   
                                            OR (tasksession12_5.state = 1048576) --  task aborted 
                                       AND last_access_time < '2015-01-01 00:00:00'  --- date time syntax
                                       tasksession12_5.state = 16384 -- audit state
                                       AND last_access_time < 30 --- for example, tasks in Audited state older than 30 days. Change the number according to your need