How to cleanup tasks directly from database?

Products

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

Issue/Introduction

Where there are huge row counts in task persistence database and runtime database the scheduled cleanup from VST (View Submitted Tasks) can be slow.  Is there any way to run the task archive process directly from the database?

 

Environment

Release : 14.x

Component : CA IDENTITY SUITE (VIRTUAL APPLIANCE)

Resolution

The CA Identity Manager vApp ships with store procedures (SPs) for cleaning up the task persistence and archive tables. They can be found in the following location:



/opt/CA/IdentityManager/IAM_Suite/IdentityManager/tools/db/taskpersistence

Instructions are below:

Tasks can be archived using stored procedure "archiveTaskPersistence" if the databases for archive and task persistence are same. The stored procedure is invoked if the "Cleanup via Stored Procedure" is enabled in Cleanup Submitted Tasks from IM user console.

Archived tasks can be cleaned up from database by calling stored procedure "archive_gcTaskPersistence". There are four parameters for the procedure:
ime_id: IM environment unique ID;
cutoff_time: date range;
chunk_rec_count: number of tasks per database transaction,
task_limit: the maximum number of rows to remove

Example1:
--SQL Server
DECLARE @now datetime;
set @now=GETDATE();
exec archive_gcTaskPersistence '1', @now, 500, 5000;

Example2:
--Oracle SQL/Plus
execute archive_gcTaskPersistence ('1', sysdate, 500, 5000);

Example3:
--MySQL:
call archive_gcTaskPersistence('1', NOW() , 500, 5000);

Example 4:

The following clears up all completed tasks older than 12 months, but will stop after processing 10,000 records

      @cutoff_time1 as datetime 

      select @cutoff_time1 = dateadd(MONTH, -12, getdate()) 

      EXECUTE garbageCollectTaskPersistence 

      @ime_id = ''1", 

      @cutoff_time = @cutoff_time1,

      @task_limit = 10000,

      @totalTasks = ''

Here's one of many links to the dateadd syntax, but please consult with your Database Administrator to verify the syntax. 

https://www.techonthenet.com/sql_server/functions/dateadd.php

Additional Information

For Virtual Appliance 14.4.x, please, see the KB article below:

Identity Manager - How to Cleanup Archive Database