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

Document ID : KB000009472
Last Modified Date : 14/02/2018
Show Technical Document Details
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.

Instructions:

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.