000031223 - Determining if RSA Identity Governance and Lifecycle database tables need to be purged

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Jun 2, 2017
Version 9Show Document
  • View in full screen mode

Article Content

Article Number000031223
Applies ToRSA Product Set: RSA Identity Governance and Lifecycle 
RSA Version/Condition: 6.9.1, 7.0
 
IssueThis document explains how to determine if an RSA Identity Governance and Lifecycle database has tables which are large and may benefit from a purge.
Large tables in a database may have the following negative impacts to the product
  • Poor performance within the UI
  • Longer and larger database backups/restores
  • Long run times for database statistics
TasksThe easiest way to get an understanding of the RSA Identity Governance and Lifecycle database size is to run a Statistics Report.
  1. From the UI select Admin > System from the menu.
  2. Click the Diagnostics tab.
  3. Click Create Report as depicted below:
Create Statistics Report

  1. After clicking Create Report and refreshing the page, there will be a new report entry placed in the table below the button. Note that the View, Download and Delete buttons are greyed out during report compilation.  
  2. Once the report is complete the buttons will be enabled.
  3. Upon clicking on the View button an HTML page will be displayed which includes a number of metrics about the current implementation.   For this document the sections of interest are entitled
  • Aveksa Database Size Summary
  • Aveksa Internal Table Summary
ASR Summary

  1. From the screen shot above, specifically the Aveksa Database Size Summary, we learn that the size of all tables summed equals 37.51 GB and the sum of all tables is 38.92 GB.  The Aveksa Internal Table Summary will then list all tables, alphabetically listed, and for each table the following is displayed 
  • Table Name
  • Number of Rows
  • Last Analyze Date, which is the date when database statistics were run on the table
  • Table Size (MB)
  • Index Size (MB)
  • Number of Indexes
  • Number of valid Indexes
  • Number of non-valid indexes
  1. One can either
    1. Scan this table and look for tables which have a large Table Size (MB) value, or
    2. Highlight and copy the entire table and paste it into a spreadsheet application, whereby one can then sort the data based upon the fourth column named Table Size (MB).
  2. If the following tables are among your top N tables, they are eligible for a purge
  • T_AV_APPLICATION_LOG
  • T_AV_JOB_STATS
  • T_AV_AFX_LOG_MESSAGE
  • T_EMAIL_LOG


T_AV_APPLICATION_LOG


Description:  
This table represents the same data represented in the aveksaServer.log.   It is governed by Admin > System Logs tab > Settings button and the field entitled Database Retention N days, as depicted:
 
T_AV_APPLICATION_LOG_CONFIG

Configuring N days will increase/decrease the table size, as will a change to debug levels captured.
Purge:  
To purge from this table one can configure Database Retention N days as described above
 

T_AV_JOB_STATS


Description:  
This table represents the data which is displayed primarily within a job detail page as from Admin > Monitoring and specifically the link named Database Logs for Run ###, for example:
 
T_AV_JOB_STATS Admin/Monitoring

And the data represented behind the link Database Logs for Run ID ### is similar to what is seen here:
 
T_AV_JOB_STATS Raw

To which this data is used so as to understand the database packages and procedures, along with duration, that were called to complete the job.  Often times, this is the largest table in the schema for a single job under Admin > Monitoring can add from hundreds to thousands of rows to this table.
Purge: 
To purge from this table one can run the following SQL statement as the Oracle database user avuser to remove all data in the T_AV_JOB_STATS table that is older than 90 days:
EXECUTE Data_Retention_Pkg.Prune_Run_Details(SYSDATE-90);

 


Description:  
If Access Fulfillment Express (AFX) is installed, this table holds log messages that are also logged in the connectors log file on the Linux host.   Data from this tab is displayed in the UI at AFX > Servers > Logs tab and the AFX > Connectors tab:
 
User-added image

 
User-added image

Purge: 
To purge data from T_AV_AFX_LOG_MESSAGE one can do the following:

Prior to version 6.9.1 


There is no procedure available to purge T_AV_AFX_LOG_MESSAGE table.

 

On version 6.9.1 


As the oracle database user run the following SQL to remove all data in the T_AV_AFX_LOG_MESSAGE table that is older than 90 days.



   EXECUTE Data_Retention_Pkg.Prune_Application_Log(SYSDATE-90);


 


On version 7.0 or later


T_AV_AFX_LOG_MESSAGE will be purged per the same parameter for purging T_AV_APPLICATION_LOG.   


T_EMAIL_LOG


Description:  
This table holds a record of every email that was sent out via the application and is equivalent to the display in the Admin > Email > Logs tab:
 
User-added image

The size of the table is governed by the configuration parameter defined in the Admin > Email > Settings tab for the value of Days to retain email log ( 0 = forever): N days
User-added image


Purge:  
As detailed above, one can control the size of T_EMAIL_LOG but the field 'Days to retain email log ( 0 = forever): days' from the Admin > Email > Settings tab.

Attachments

    Outcomes