How do I improve performance of my snapshot database for enhanced reporting in CA Identity Manager r12?

Products

• CA Directory
• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Risk Analytics
• CA Secure Cloud SaaS - Arcot A-OK (WebFort)
• CLOUDMINDER ADVANCED AUTHENTICATION
• CA Secure Cloud SaaS - Advanced Authentication
• CA Secure Cloud SaaS - Identity Management
• CA Secure Cloud SaaS - Single Sign On
• CA Security Command Center
• CA Data Protection (DataMinder)
• CA User Activity Reporting

Issue/Introduction

This technical document outlines the steps necessary for adding indexes to the heavily used snapshot database tables to improve the data write operation during a snapshot. This applies to CA Identity Manager r12 CR10 or later.

For further information of the details of the issue, please see the Release Notes for CA Identity Manager r12.0 CR10; the solution outlined in the "Solution" section should be performed in addition to the steps outline here.

Environment

Release: CAIDMB99000-12.6.8-Identity Manager-B to B
Component:

Resolution

In order to improve the snapshot export performance, the following steps should be performed in order to add needed indexes. Also provided are the commands to remove these indexes if needed.

1. Stop the application server where CA Identity Manager r12.0 CR10 (or later) is installed.
   
   
2. Check the type of the snapshot reporting database.
   
   
3. If it is MS SQL Server, use the following:
   
   Please Note: This section provides steps for both creating the index and removing those indexes, if needed.
   
   
   1. Creating the index:
      
      CREATE INDEX imrrolemember6_index_3 ON imrrolemember6 (imr_reportid,imr_roleoid)
      INCLUDE (imr_userdn)
      go
      
      CREATE INDEX imrroleadmin6_index_3 ON imrroleadmin6 (imr_reportid,imr_roleoid)
      INCLUDE (imr_userdn)
      go
      
      CREATE INDEX imrroleowner6_index_3 ON imrroleowner6 (imr_reportid,imr_roleoid)
      INCLUDE (imr_userdn)
      go
      
      CREATE INDEX imruser6_index_3 ON imruser6 (imr_reportid)
      INCLUDE (imr_userdn)
      go
      
      CREATE INDEX imrorg6_index_2 ON imrorg6 (imr_reportid)
      INCLUDE (imr_orgdn)
      go
      
      CREATE INDEX imrgroup6_index_2 ON imrgroup6 (imr_reportid)
      INCLUDE (imr_groupdn)
      Go
      
      
   2. Removing the index (only if needed):
      
      DROP INDEX imrrolemember6.imrrolemember6_index_3;
      
      DROP INDEX imrroleadmin6.imrroleadmin6_index_3;
      
      DROP INDEX imrroleowner6.imrroleowner6_index_3;
      
      DROP INDEX imruser6.imruser6_index_3;
      
      DROP INDEX imrorg6.imrorg6_index_2;
      
      DROP INDEX imrgroup6.imrgroup6_index_2;
      
      
4. If it is Oracle, use the following:
   
   Please Note: This section provides steps for both creating the index and removing those indexes, if needed.
   
   
   1. Creating the Index
      
      CREATE INDEX imrrolemember6_index_3 ON imrrolemember6 (imr_reportid,imr_roleoid, imr_userdn);
      
      CREATE INDEX imrroleadmin6_index_3 ON imrroleadmin6 (imr_reportid,imr_roleoid, imr_userdn);
      
      CREATE INDEX imrroleowner6_index_3 ON imrroleowner6 (imr_reportid,imr_roleoid, imr_userdn);
      
      CREATE INDEX imruser6_index_3 ON imruser6 (imr_reportid, imr_userdn);
      
      CREATE INDEX imrorg6_index_2 ON imrorg6 (imr_reportid, imr_orgdn);
      
      CREATE INDEX imrgroup6_index_2 ON imrgroup6 (imr_reportid, imr_groupdn);
      
      
   2. Removing the index
      
      DROP INDEX imrrolemember6_index_3;
      
      DROP INDEX imrroleadmin6_index_3;
      
      DROP INDEX imrroleowner6_index_3;
      
      DROP INDEX imruser6_index_3;
      
      DROP INDEX imrorg6_index_2;
      
      DROP INDEX imrgroup6_index_2;
      
      
5. To run these scripts, open the appropriate database management editor (for ex. for SQL 2005, you can use "SQL Server Management Studio" and for Oracle either "SQL * Plus" or "SQL Developer Studio").
   
   
6. Restart the application server.