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 can 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 records for a particular JobScheduler which has ben removed.
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 set up 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 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 scriptUsage: 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 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:
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
./bin/cleanup_inventory_tables.(sh|cmd)
In version 1.11.1 and older the inventory data remains in the Reporting database when you uninstall the JobScheduler. Similarly, if you modify the JobScheduler Id or HTTP port then a new set of inventory data will be written and and the obsolete set of inventory data remains.
In the JOC Cockpit you will still be able to view inventory data for a JobScheduler which is not longer active until the obsolete data is removed from the Reporting database.
Note that with version 1.11.2 and newer inventory data is deleted when a JobScheduler is uninstalled. SET-96 - Getting issue details... STATUS
The ./bin/cleanup_inventory_tables.(cmd|sh) script can be used to remove old 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 tablesExample 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