DB Maintenance with Automic utilities

Discussion created by Antoine_Sauteron_1266 on Jul 21, 2016
Latest reply on Oct 16, 2018 by Michael_Pirson
Database maintenance is quite the hot topic in an Automation Engine environment, as the system's performance is highly dependent upon the DB's response time.
Maintenance has to be performed with Automic utilities DB Archive, DB Reorg and DB Unload on a regular basis.

A/ Principle

1 - Reorganized data

The Automic DB contains a lot of data related to jobs processing and object versions. Here is the list of tables that are affected by maintenance:
  • MELD :contains messages [visible in the message window of the GUI].
  • RH and RT :contain all information related to execution reports.
  • AH : contains statistics.
  • OH : Contains all the objects (JOBS, EVNT, USER, CLNT, ...) and their versions [visible in the documentation tab]
  • XAO :Records the history of modifications against objects, which can consulted through theRevision Report.
2 - Automic Utilities

B/ How it works

1 - Adjusting the retention period

  • With utilities (DB Archive / DB Reorg) in graphical mode:

2 - Maintenance workflow

Preferred way of maintaining the DB is to use a workflow that will run utilities one after the other. An example template (v10/v11 compatible) is attached to this post. Please feel free to import it and use it in your environment.

  • VARA.SQLI.CLIENT.LIST: lists all clients.
  • JOBP.FOREACH.ARCHIVE.REORG: recursively runs DB Archive and DB reorg on all clients.
  • JOBP.MAINTENANCE: contains the above workflow + unload job.


Q : How far back should be records kept?
A : It totally depends on the amount of tasks run in your environment, more specifically the number of records generated by tasks and stored in the DB. 30 days is usually a pretty safe option.

Q : Are all maintenance steps mandatory? Or is there a way of speeding up the maintenance?
A : Only Reorg and Unload are necessary to remove entries from the DB. DB Archive makes backup copies but does not remove anything.

Q : How often should maintenance be performed?
A : The more often you will run it, the fewer time it will take. As mentioned before, it will depend on your environment. Once a week is generally sufficient.

Q : How does maintenance impact the overall performance while being run? Should a downtime be planned?
A: Maintenance can take up to several hours if it's not run on a regular basis. DB Unload is usually the longest taking one, as it performs DELETE statements against the DB.

Q : What are the risks of not maintaining a DB with the utilities?
A : Performance will keep getting worse, until the Automation Engine becomes totally unavailable.

Q : Aside from maintenance with the utilities, should anything else be performed on the DB side?
A :  Rebuilding indexes and shrinking the DB / tables can also be performed on a regular basis. These should be done by a DBA.