Reconciliation performance issues caused by too many records in the RE:Job_Runs forms.

Version 6
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    BMC Atrium Core - CMDB


    COMPONENT:

    BMC Atrium Core


    APPLIES TO:

    BMC Atrium CMDB - in relationship to the solution of this issue, this article applies to all versions of Reconciliation Engine.



    PROBLEM:

     

    Customers may experience poor performance with reconciling or looking at job history in the Reconciliation console (Atrium Core Console). 
    After checking the RE archive and job tables we found ~50,000 records in form RE:Job_Runs and ~200,000 in RE:Job_Events.
    Deleting these records enabled us to work again with RE console.


     

     


    CAUSE:

    Too many records in RE job status forms. Forms are Reconciliation Job runs and Reconciliation Job Events.


    SOLUTION:

     

    As a work around you can create an AR Escalation feature that can run once a week that deletes all RE:Job_Runs and RE:Job_Events records that have a run status = success AND* are greater than a certain age e.g.
    Run End Time > $ TIMESTAMP$ - 604800 (1 week in number of seconds) AND run status = success.

    *The "AND" in the solution above is important because Reconciliation uses the Modified Date of the CI or Relationship and compares it with the last successful Merge activity found in RE Job Events. 
    If you delete all records then no delta can be performed by Reconciliation that uses the Merge Order of Together in one (or separate) transactions. With these records gone it will take longer to complete the next Job. 

    Advanced admins only:
    Clearing of these records can be done by a thick client like the legacy User Tool (midtier will only allow a limited number of records to be selected) or directly using a SQL query. 
    However, it is not possible to delete from the dbo.RE_JOB_Runs or dbo.RE_JOB_Events. Tables are constructed from multiple tables and although that should be technically possible by having a view or join, in this case you will get this error when using this command in SQL:

    SQL statement: delete from RE_JOB_Runs where Run_End_Time > (GETDATE()- 604800)
    ERROR: View or function 'RE_JOB_Runs' is not updatable because the modification affects multiple base tables.

    To overcome this you need to get the "SchemaID" for RE:Job_Runs and RE:Job_Events from the ARSCHEMA table. 

    SQL statement: select name, schemaid from arschema where name like 'RE:Job_%'

    The results will have a list of tables and their SchemaId as in the example below: 

                                                                                                                                                                                                                                                                                
    NameSchemaId
    RE:Job Operation900
    RE:Job Operation Simplified901
    RE:Job_Activity_AsscGrp_Join903
    RE:Job_Events954
    RE:Job_Events_Archive955
    RE:Job_JobRuns_Events_Join956
    RE:Job_JobRuns_Join961
    RE:Job_Runs962
    RE:Job_Runs_Archive963
    RE:Job_Schedules964
    RE:Job_Simplified965
    RE:JobRuns_Events_Join966
    RE:Jobs History Console970
      


    Now you can delete data from these tables by pre-pending a "T" the SchemaId.

    Warning: Please make sure you have full understanding of this action before performing it! It will delete RE job related data older than a month, but if there is any reason why you'd need to preserve this information then please backup the ARSCHEMA DB, or This Table, or create a report. This SQL query is destructive and will delete data!

    Here is an example of such query for MSSQL based on the above results. 

      

    select * from T954 where C301068500 <= (Select DateDiff(S, '19700101', dateadd(month, -1, GetDate())))
    and
    select * from T962 where C301068500 <= (Select DateDiff(S, '19700101', dateadd(month, -1, GetDate())))


     

     


    Article Number:

    000052300


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles