Service Operations Insight

Problem: The database migration takes a long time to complete due to a huge amount of data stored in the ENTITY_CONFIG table.

Solution: Deactivate unused entities and delete them from the ENTITY_CONFIG table. Follow these steps:

1. Deactivate unused entities as described in the CCC 2.9.3 documentation here: https://docops.ca.com/ca-capacity-management/2-9-3/en/administrating/capacity-command-center-administration/deactivate-and-remove-unused-entities/deactivate-unused-entities/
2. Execute the following script and make a backup copy of the inactive entities in your DM database. Change the insert_date parameter to the date up to which you want to back up inactive entities:
   

    create table entity_config_bkp
    as (SELECT * from entity_config A 
        where a.entity_id IN (SELECT entity_id FROM entity B 
                              WHERE B.entity_id=A.entity_id
                              and   EXISTS (SELECT 1 FROM entity_inactive_view C WHERE C.guid = b.guid ))
    and trunc(insert_date) < TO_DATE('01-JAN-14'));

3. Delete inactive entities from the DM database using the following query. Change the insert_date parameter to the date up to which you want to delete inactive entities:
   

     delete from entity_config A
     where a.entity_id IN (SELECT entity_id FROM entity B WHERE B.entity_id=A.entity_id
                                       and  EXISTS (SELECT 1 FROM entity_inactive_view C WHERE C.guid = b.guid ))
     and trunc(insert_date) < TO_DATE('01-JAN-14');
     Commit;

4. After deleting the inactive entities, run the following code to analyze the table and rebuild indexes:
   

   BEGIN
         FOR table_entry IN (SELECT table_name FROM user_tables WHERE table_name ='ENTITY_CONFIG')
         LOOP
             EXECUTE IMMEDIATE 'ANALYZE TABLE ' || table_entry.table_name || ' ESTIMATE STATISTICS';
         END LOOP;
   
         FOR index_entry IN (select INDEX_NAME from user_indexes where table_name ='ENTITY_CONFIG')
         LOOP
             EXECUTE IMMEDIATE 'ALTER INDEX ' || index_entry.INDEX_NAME || ' REBUILD';
         END LOOP;
    END;
   

   Note: Deleted inactive entities are not included in the “Inactive Entity Report”.
   
   
5. Verify that the deployment was successful:
   
   1. Connect to SQL Developer.
   2. Count the records in the ENTITY_CONFIG table.