Cleaning up Task Persistence DB of unnecessary data

Products

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

Issue/Introduction

Introduction:

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.

 

 

Environment

Identity Manager-14.x
Component:

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

Cause

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

Resolution

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

NOTES:

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
                                       )
                                       OR
                                       (
                                       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
                                       )
                                );