Database Maintenance Scripts

Scope

This article describes database maintenance tasks that should be carried out on a regular basis. It applies for JobScheduler 1.11 and newer. FEATURE AVAILABILITY STARTING FROM RELEASE 1.11

See the Job JobSchedulerCleanupHistory article for information about database maintenance tasks for JobScheduler older than 1.11.

Introduction

The JobScheduler writes information about each Task and Order it processes into database tables. Without regular maintenance, these tables can become very large which can reduce performance and can eventually result in the DBMS running out of space.

Two command line scripts for database maintenance are provided with the JobScheduler from release 1.11.x and newer. These scripts replace the JobSchedulerCleanupHistory JITL Job, which is not included in the JobScheduler releases 1.11.0 onwards.

Note that all the JobSchedulers accessing the database tables listed in the next section should be stopped before the scripts are run. This would apply, for example, to all the JobSchedulers in a cluster.

Database Table Usage

The JobScheduler writes information about each task and order into the following database tables:

• SCHEDULER_HISTORY
• SCHEDULER_ORDER_HISTORY
• SCHEDULER_ORDER_STEP_HISTORY

The sos/dailyplan/CreateDailyPlan job writes to the table:

• DAILY_PLAN

The sos/jade/jade_import job writes to the tables:

• JADE_FILES
• JADE_FILES_HISTORY

The plugin com.sos.jitl.reporting.plugin.FactPlugin writes to the database table:

• REPORTING_*

Database Cleanup Scripts

The JobScheduler can be setup to use two databases from version 1.11.x onwards; the JobScheduler database and the Reporting database. A separate cleanup script is provided for each database.

These scripts can be found as follows in the JobScheduler installation directory :

• ./bin/cleanup_jobscheduler_tables.(sh|cmd)
• ./bin/cleanup_reporting_tables.(sh|cmd)

./bin/cleanup_jobscheduler_tables.(sh|cmd)

• This script cleans the following database tables:
  • SCHEDULER_HISTORY
  • SCHEDULER_ORDER_HISTORY
  • SCHEDULER_ORDER_STEP_HISTORY
• It uses the database connection setting stored in:
  
  • ./config/hibernate,cfg.xml.
• This script has only one argument - for the age of the records.
  
  • All records older then the specified age are removed.

• If this script is called without an argument it will ask for a parameter as follows:

  Use of the cleanup_jobscheduler_tables script

  Usage: cleanup_jobscheduler_tables.(sh|cmd) age          | age : Age of table entries in days
  
  Example: - To remove entries older than 14 days call:
             cleanup_jobscheduler_tables.cmd 14
           - To remove all entries call:
             cleanup_jobscheduler_tables.cmd 0

./bin/cleanup_reporting_tables.(sh|cmd)

• This script cleans the following database tables:
  • DAILY_PLAN
  • JADE_FILES
  • JADE_FILES_HISTORY
  • REPORTING_*
• It uses the database connection setting stored in:
  • ./config/reporting_hibernate,cfg.xml.
• This script has two arguments: for the age of the records and for the range of the tables.
  
  • All records older then the specified age are removed.
  • The range argument is used to filter the tables in which the records will be removed from. Possible values are all, reporting, dailyplan or yade.

• If this script is called without an argument it will ask for parameters as follows:

  Use of the cleanup_reporting_tables script

  Usage: cleanup_reporting_tables.(sh|cmd) range age
         range           | which tables? all, reporting, dailyplan, yade
         age             | Age of table entries
  Example: - Remove entries older than 14 days in all tables then call
             cleanup_reporting_tables.cmd all 14
           - Remove entries older than 14 days in reporting tables then call
             cleanup_reporting_tables.cmd reporting 14
           - Remove all entries then call
             cleanup_reporting_tables.cmd all 0