Tech Tip: HOTFIX for ‘Data Migration takes too long due to the large data volume in the ’ENTITY_CONFIG’ table (DE245810)

Document created by biecl01 Employee on Feb 21, 2017Last modified by biecl01 Employee on Feb 21, 2017
Version 2Show Document
  • View in full screen mode

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.

    Attachments

      Outcomes