Skip to end of metadata
Go to start of metadata

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 versions 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 eventually result in the DBMS running out of space.

Two command line scripts for database maintenance (cleanup_jobscheduler_tables.(sh|cmd) and cleanup_reporting_tables.(sh|cmd)) 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 and later. These scripts delete database records of a particular age and older.

In addition a script is provided (./bin/cleanup_inventory_tables.(cmd|sh)) for deleting database inventory records. These can remain when a JobScheduler older than version 1.11.2 has been deleted or when the ID or Port of a JobScheduler is changed.

 

IMPORTANT NOTE

Icon

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

 

Database Table Usage

JobScheduler Tables

The JobScheduler writes information about each Task and Order into the following database tables:

  • SCHEDULER_HISTORY
  • SCHEDULER_ORDER_HISTORY
  • SCHEDULER_ORDER_STEP_HISTORY

Reporting Tables

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 tables:

  • REPORTING_*

The plugin com.sos.jitl.inventory.plugins.InitializeInventoryInstancePlugin writes to the database tables:

  • INVENTORY_*

Database Cleanup Scripts

From version 1.11.x onwards the JobScheduler can be set up to use either one or two databases: the JobScheduler database can either be used for both JobScheduler and Reporting tables or the Reporting Tables can be written to a separate Reporting database. A separate cleanup script is provided for each set of database tables.

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_inventory_tables.(sh|cmd)

Clean up jobscheduler Tables

  • This script cleans the following tables in the JobScheduler database:
    • 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

Clean up Reporting Tables

  • This script cleans the following tables in the Reporting database:
    • 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

 

Clean up Inventory Tables

There are situations where the removal of inventory data from the Reporting database may be required. These are:

  • In version 1.11.0 and 1.11.1 the inventory data remains in the Reporting database when a JobScheduler is uninstalled.
    Note that with version 1.11.2 and newer inventory data is automatically deleted when a JobScheduler is uninstalled. SET-96 - Script to remove an old JobScheduler instance from the inventory tables Released
  • If the JobScheduler Id or HTTP port are modified. This causes a new set of inventory data to be written and allows the obsolete inventory data set to remain.

If obsolete inventory data is not removed from the Reporting database then it will still be possible for users with the necessary permissions to view this information in the JOC Cockpit.

The ./bin/cleanup_inventory_tables.(cmd|sh) script is used to remove obsolete inventory data.

  • This script cleans all INVENTORY_* database tables in the Reporting database.
  • It expects the database connection setting in ./config/reporting_hibernate.cfg.xml.
  • This script has three arguments to identify a specific JobScheduler instance. These are
    • JobScheduler Id
    • Host
    • HTTP port
  • With the single argument info a list of existing JobScheduler instances from the inventory will be displayed.
    • This call is helpful to see the exact values of Id, host and HTTP port.
  • If this script is called without an argument then you get its usage:

  • Example for an info output:

Clean up Audit Log Table

FEATURE AVAILABILITY STARTING FROM RELEASE 1.12.6

  • This script cleans the following table in the Reporting database:
    • AUDIT_LOG
  • It uses the database connection setting stored in:
    • ./config/reporting.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