Best practices for archiving/purging Workload Automation DE (dSries) database

Document ID : KB000009456
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

Best practices for archiving and purging Workload Automation DE (dSries) database for improved server performance.

Environment:
CA Workload Automation DE (dSeries) 11.1.x
Instructions:

The best practices for archiving and purging Workload Automation DE (dSries) database for improved server performance are;

  1. Remove active and completed jobs information from server repository by doing the following;

    1. Force complete active application generations in trouble or having failed/suberror jobs for long time to make sure that -

      • DE application server is not caching unwanted data.

      • Maximum number of active application generation threshold "active.appl.gen.stop=" as setup in espresso.properties file (located in DE Server installation directory) is NOT exceeded. If the threshold is exceeded DE Server is forced to shutdown.

    2. Execute 'purgecompletedjobs' CLI command or setup a job to execute the 'purgecompletedjobs' CLI command and schedule the job to run daily for all applications (for example, over 7 days old or less) depending on the volume of workload and job run frequency.

  2. Periodically scale down job historical data by deleting 'excessive' records from ESP_APPLICATION and ESP_GENERIC_JOB tables in DE Server database. Steps to follow are;

    1. Stop the  application server.

    2. Issue single SQL statement to clear data from both database ESP_APPLICATION and ESP_GENERIC_JOB tables. For example;

      DELETE from ESP_APPLICATION where END_DATE_TIME < TO_DATE('2010-09-01','YYYY-MM-DD') and STATE like 'COMPLETE';

      The SQL statement will delete, using the END_DATE_TIME date job historical data from both ESP_APPLICATION and ESP_GENERIC_JOB tables due to DE database schema uses cascade delete and therefore sets up dependencies between rows in the tables. It is recommended to keep few months old data and DO NOT specify a date that will impact active workflow in the SQL statement.

    3. Index on ESP_APPLICATION table.
Additional Information:

CA Workload Automation DE 11.3 SP1 and higher:

Guidelines for Cleaning Up Old Generations from the Database

The ESP_APPLICATION,ESP_GENERIC_JOB table tends to grow over time and requires trimming or cleaning to create space. We recommend that you periodically clean the CA Workload Automation DE database. As jobs complete, the database continues to grow with records of completed jobs.
The server collects information about completed Applications and stores this information in the relational database. Over time, the history data can become huge. To create more disk space for the database and improve server performance, you can issue the MOVEHISTORYDATA command to move some of the history information in the database tables to stage tables.

Note: When the data has been moved, a message is added to the tracelog.txt file indicating the number of Applications and rows that were moved. For more information about the trace log, see Maintaining.

This command has the following format:
movehistorydata olderthan("olderthan")
olderthan("olderthan")
Moves history data older than the specified time to stage tables.

Note: The value must be a valid schedule criteria statement that resolves to a date and time. For more information about a valid schedule criteria statement, see Schedule Criteria.

Example: Move History Data Older than a Specified Date
The following example moves all the history data that is older than a month to stage tables:
movehistorydata olderthan("now less 1 month") 

We recommend that you force complete Applications that have been active for a long time because of failed jobs or jobs with submission errors (suberror). Force completing old Application generations helps prevent the server from caching unwanted data.

We also recommend that you issue the PURGECOMPLETEDJOBS CLI command regularly to purge completed Application generations. You can schedule the PURGECOMPLETEDJOBS command to run once a day for Applications over seven days old. For certain Applications that run on a more frequent basis, you can schedule it to run every two hours.

movehistorydata Command—Move History Data to Stage Tables

Set Up a Housekeeping Application