Cleanup Submitted Tasks Not Working; Database stored procedure for cleaning up task persistence is available.
Article ID: 43199
Updated On:
Products
Issue/Introduction
Tasks Persistence tables have become large, which negatively impacts system performance. The Cleanup Submitted Tasks task may have no effect in cleaning up the tables because it is not intended to cleanup massive numbers of rows, rather it is designed to maintain weekly or daily cleanups of an already well maintained database.
Environment
CA Identity Manager 12.x
CA Identity Manager 14.x
CA Identity Suite 14.x
Resolution
For cleaning up large numbers of completed tasks use the built-in database stored procedure.
By default, the instructions for running the stored procedure are located in the readme.txt here:
..\CA\Identity Manager\IAM Suite\Identity Manager\tools\samples\TaskPersistence\SQLServer
..\CA\Identity Manager\IAM Suite\Identity Manager\tools\samples\TaskPersistence\Oracle
For Virtual Appliance 14.4.x, see Additional Information below
Use the script exec_sqlserver_gc.sql to run the stored procedure.
The example in the readme.txt is...
select @cutoff_time1 = dateadd(minute, -5, getdate())
...which means delete everything but the most recent 5 minutes of data.
The main idea is to use the SQL dateadd command which has many options available.
You'll probably want to delete only the oldest week or month, or so, to start and ramp up once you're comfortable with the number of rows deleted and the time required.
So if you have task persistence data that is 16 months old, for example, you might start by deleting everything older than 15 months. This would look something like this...
dateadd(month, -15, getdate())
For example,
@cutoff_time1 as datetime
select @cutoff_time1 = dateadd(MONTH, -15, getdate())
EXECUTE garbageCollectTaskPersistence
@ime_id = ''1",
@cutoff_time = @cutoff_time1,
If you do want to take advantage of these two new required parameters:
>Task limit is a number that would limit the number of tasks that would get archived. It's useful mainly when you don't know the volume of tasks that qualify (which at times can be millions), and you want to limit the amount of resources the DB needs to complete the procedure. For example, 10000, meaning after it cleans up 10k tasks it would stop even though there are more qualified transactions under your criteria.
>The total tasks parameter is an output parameter - when a task gets archived it increases this by one and when it's completed you'd get Total Tasks output saying how many transactions got cleaned up.
For example, 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 = ''
Additional Information
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
For information on cleaning task persistence in a cross-database deployment scenario please refer to the Knowledge base article below:
Cleaning Archive and Task Persistence data in a Cross-Database scenario
For Virtual Appliance 14.4.x, the Admin Tools was moved to the "Symantec IGA Virtual Appliance Remote Tools r14.4" package
1. To download it, access the site https://support.broadcom.com
2. Click "Symantec Enterprise Security"
3. Click Product Downloads.
4. Search and click "IDENTITY MGMT AND GOVRNCE"
5. On the next page, search for "Identity Suite MULTI-PLATFORM", check the Release is 14.4
6. Search for the"Symantec IGA Virtual Appliance Remote Tools r14.4" package and download it
After downloading it, uncompress it and you will find the file "CA-IM_Tools.zip"
After uncompressing it, you will find the readme.txt file under the "\CA-IM_Tools\samples\TaskPersistence\<Database>" folder
Feedback
