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 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.

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

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

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

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

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 - Getting issue details... STATUS
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:

Usage: cleanup_inventory_tables.(cmd|sh) [JobSchedulerId JobSchedulerHost JobSchedulerHTTPPort] | [info]
       JobSchedulerId           | The Id, hostname and http port of a
       JobSchedulerHost         | JobScheduler which data should be
       JobSchedulerHTTPPort     | removed in the INVENTORY tables of the
                                | reporting database.
   or
       info                     | Shows a list of existing JobScheduler
                                | instances in the INVENTORY tables

Example for an info output:

JobSchedulerId                   | Host                             | Port
---------------------------------------------------------------------------
scheduler.1.11-reporting         | OH                               | 40413
scheduler.1.11-x86               | OH                               | 40186
SP_41110x1                       | SP                               | 40119
scheduler.1.11                   | OH                               | 40411
scheduler.1.11-reporting         | OH                               | 40423
scheduler.1.11.1                 | OH                               | 40471

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.