uCMDB Support Tip : uCMDB History Database Purging and Maintenance mechanism
History Database can collect a large number of historical CI data over a period of time.This updated article provides a detailed description about UCMDB History Database Purging mechanism and its maintenance.
HP UCMDB purges the stored history data periodically. There is a scheduled task on the server that purges data based on default Infrastructure Settings. The default settings schedules purging once every 24 hours at night[1am]. The number of default days to keep history changes is 90 days, the older data is purged daily every night. There are few other JMX console operations available to manage history data explained below. Please see the troubleshooting section below for monitoring logs for daily history scheduler purge activities
For optimal performance and history database application maintenance, the Database Adminstrator should review the database guide for the respective database used by UCMDB Application. There is comprehensive information available about database settings and maintenance. It is important that database application should comply with the information provided in the database guide. Please review UCMDB 9.05 Database Guide available here Document ID: KM134785 Database Guide. UCMDB 10.01 Database Guide is available here Document ID: KM00245428 Database Guide
History Setting can be accessed from the UCMDB UI under Administration-->Infrastructure Settings Manager-->History Settings[under Categories section]. Following three settings are listed below with their default values.
HistoryDays to Save Back= 90
History Scheduler Hour of the First Run=1
History Scheduler Interval=24
Hide empty snapshots=True
Hide empty snapshot older than=3
History Months to Save Back=3
History purging extra months to save back removed data=1
History Scheduler Hour of the First Run=1
History Scheduler Interval=24
History JMX console[UCMDB 9.0x]:
The UCMDB Server JMX console provides some functions to maintain the history database. In order to access JMX console please see Document ID: KM200930 - How to access the UCMDB/MAM Server's JMX Console . On the JMX page, please click on the link UCMDB:service=History DB Services to reveal several other operations related to history database. A short summary of each operation is available.
- clearHistoryDB Delete the data from the history DB [This operation truncates all tables in the history database]
- fix902EndTimeRecords Perfrom a fix of history DB records affected by UCMDB 9.02 end_time bug
- getHistoryChangesCounterByType Get number of changes according to class type and time
- getHistoryChangesExtendedCounter Get number of changes according to id and time (including relations)
- getHistoryDBContext Get history DB context
- getRemovedData Get data that was removed according to the time
- initializeHistoryDBFromModel Initialize the history DB with the current data in the model [This can be run after clearHistoryDB so history DB is initialized after clear history]
- isHistoryEnabled Returns true if history is enabled
- purgeHistoryDB Purge the history DB according to the input of how many days to save
- purgeHistoryDBAccordingToSettings Purge the history DB according to the settings
- resetHistoryEventsCountForStatisticsCalculations Reset the count of history events for statistics calculations
- runStatistics Run statistics on the history DB
- showStatisticsCounters Show current statistics counters
- startHistoryDB Start the history DB for saving and publishing changes
- stopHistoryDB Stop the history DB from saving and publishing change
History Database Purge (on demand UCMDB 9.0x):
Unlike the scheduled task, history database can also be purged on demand using the following JMX function.
Following Values can be used
Where <x> is an integer value. It is the number of days to save in the history database. A value of 6 for daysToSaveBack means that last six days of CI history changes will be saved and rest of the changes will be purged. Please note in UCMDB 9.03 there was a defect QCCR1H63335 reported, where this JMX operation was using "daysToSaveBack" value from "History Settings" values stored in "Infrastructure Settings" instead of value provided in the parameter of daysToSaveBack for JMX. This is fixed in 9.05 release of UCMDB.
For troubleshooting and logs for history database see page 17 of UCMDB 9.05 Administration Guide Document ID: KM477585 Administration Guide.
History Database Operation Timeout:
This can happen when running manual history database JMX operations or the periodic scheduler. It happens because there are large number of records that need to be deleted and queries running against the database takes larger to execute then the default timeout for the database queries used by UCMDB Application. Error similar to following will be noticed in the logs, if such symptoms are noticed, it is advised to use the offline sql script to purge history database.
....Can't execute prepared statement [delete from HIST_EVENTS where.................. java.sql.SQLTimeoutException: [mercury][Oracle JDBC Driver]Execution timeout expired....
In such cases, an offline purging mechanism is available i.e. there are database scripts provided that can be run on the database server when UCMDB Application service is shutdown. Please download the scripts below for the respective database vendor. Each is bundled with Readme.txt, please follow the instruction in Readme.txt. These scripts are compatible with any UCMDB 9.0x version great than 9.02. The following scripts were provided as workaround for UCMDB 9.0x and are not compatible with UCMDB 10.x, if there are timeout issue with UCMDB 10.x, please open a case with support.
- Offline database history Purge sql script for Oracle
- Offline database history Purge sql script for MS-SQL
Limitation of History Database Purge in 9.0x.
History Database stores events in history database for each CI. Following types of events are created as a record in history table i.e. 'AddObject', 'UpdateObject', 'AddLink', 'RemoveObject', 'RemoveLink', ;AddLink' etc. Event like 'AddObject' and 'AddLink' are created as a record in history when CIs/Links are created. Related to each event there is a separate record created for each attribute in a separate table in database. The events such as 'RemoveObject' and 'RemoveLink' are always kept in history database, along with the last known values of the attribute changes related to these events. So if a CI is deleted the information about when it was deleted with the last known values of the attribute will always be kept in history database. In other words these type of records are never deleted by the scheduled purge tool or by JMX purge operation. So a very old history database can grow in size if it contain several value of deleted CIs/links and the last known values of attributes that were cumulatively stored in history database over a period of time.
This is the limitation in design of UCMDB 9.0x history database that such data cannot be removed. Depending on requirement for history database, clearHistoryDB operation in JMX can be used to truncate all history database and start fresh. UCMDB 10.x history DB is redesigned to overcome this limitation and delete any data as desired.
**Please note that there maybe attributes of CI in history database that will always be kept even they are older than 90 days or days to save back settings. These historical record about a CI attribute is not deleted by any purging mechanism/tool. This is part of the design. The reason they are kept is because they were the last known values and are consumed by UCMDB. If there is a newer value of same attribute for a CI is added in history then the old value gets purged**
History Database and Improvement in UCMDB 10.x:
History Database storage and management is redesigned in UCMDB 10.x to overcome limitations in purging mechanism as mentioned above and performance of history database in UCMDB 9.0x. There are much better JMX operations are available with 10.0x release. For more information about History Database Storage and Purging. Please review UCMDB 10.01 Administration Guide, please see pages 13-14. of UCMDB 10.01 Administration Guide : Document ID: KM00245425 Administration Guide.
Bookmark the document for future reference : http://support.openview.hp.com/selfsolve/document/KM522386
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
Click the KUDOS star to say 'Thanks'