Product Knowledge Base

Space shortcuts

Page tree

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. Such records can remain in place if a JobScheduler earlier than release 1.11.2 has been deleted or if the ID or port of a JobScheduler is changed.


IMPORTANT NOTE

Note that all JobScheduler Master instances accessing the database tables listed below should be stopped before the scripts are executed. This applies, for example, to all JobScheduler Master instances in a cluster.


Database Table Usage

JobScheduler Tables

The JobScheduler writes information about each Task and Order to 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 release 1.11.x onward 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 managed with 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 settings stored in:
    • ./config/hibernate,cfg.xml.
  • This script makes use of only one argument - the age of the records to be deleted.
    • All records older than the specified age are removed.

  • If this script is called without an argument then 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 up the following tables in the Reporting database:
    • DAILY_PLAN
    • JADE_FILES
    • JADE_FILES_HISTORY
    • REPORTING_*
  • The script uses the database connection settings stored in:
    • ./config/reporting_hibernate,cfg.xml.
  • This script makes use of two arguments: for the age of the records and for the range of the tables.
    • All records older than the specified age are removed.
    • The range argument is used to filter the tables from which the records will be removed from. Possible values include all, reporting, dailyplan or yade.


If this script is called without an argument then 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 when the removal of inventory data from the Reporting Database may be required. These are:

  • In releases 1.11.0 and 1.11.1 the inventory data remain in the Reporting Database if a JobScheduler Master is uninstalled.
    Note that with releases 1.11.2 and newer inventory data is automatically deleted if a JobScheduler Master 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 in place.

If obsolete inventory data are 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 settings 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 "remove".
    • This call determines the three parameters above automatically for the instance from which this script is started and cleans all INVENTORY_* database tables for this instance only.
      • This means that only the entry for this JobScheduler Master will be removed from the INVENTORY_INSTANCES table.
      • Additionally all entries from all other INVENTORY_* tables related to this JobScheduler Master instance will be removed also.
      • In case of a cluster, only the cluster member from which the script was started will be removed from the INVENTORY_* tables. Therefore consider to execute the script from the installation directory of each JobScheduler Master cluster member.
    • Be careful to make sure this instance is not running when executing this script with the single parameter "remove".
  • With the single argument info a list of existing JobScheduler Master instances from the inventory will be displayed.
    • This call is helpful to see the exact values of SCHEDULER_ID, host and HTTP port.

  • If this script is called without an argument then you get its usage clause:

    Usage: cleanup_inventory_tables.(cmd|sh) [JobSchedulerId JobSchedulerHost JobSchedulerHTTPPort] | [info] | [remove]
       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
   or
       remove                   | Determines the parameters automatically
                                | for the instance in which the cleanup was
                                | started and removes this instance from
                                | the database.
                                | CAUTION! Make sure beforehand
                                | that this instance is not running!

  • 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 settings stored in:
    • ./config/reporting.hibernate,cfg.xml.
  • This script makes use of only one argument - the age of the records.
    • All records older than the specified age are removed.

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

    Use of the cleanup_jobscheduler_tables script
    Usage: cleanup_audit_log_table.(sh|cmd) age          | age : Age of table entries in days

Example: - To remove entries older than 14 days call:
           cleanup_audit_log_table.cmd 14
         - To remove all entries call:
           cleanup_audit_log_table.cmd 0