Scope

Provide technical information on the data model and possible customizations.
Users can use this information to create their own reports from the data model.
SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

Standard Data Model

Standard Tables

Inventory Tables

The following tables are managed by reporting inventory jobs that create an inventory of existing jobs, job chains and orders.
Records are overwritten by each run of the reporting inventory jobs.
SQL Scripts
- Tables: inventory.sql

INVENTORY_INSTANCES

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	VARCHAR		corresponds to the `SCHEDULER_ID` that is specified during installation of the JobScheduler instance
`HOSTNAME`	NOT NULL	VARCHAR		host on which the JobScheduler instance has been installed
`PORT`	NOT NULL	NUMERIC		TCP port that the JobScheduler instance is listening to. Should not TCP port be specified then the UDP port is provided.
`LIVE_DIRECTORY`	NOT NULL	VARCHAR		path of the `live` directory
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

INVENTORY_FILES

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_INSTANCES.ID`
`FILE_TYPE`	NOT NULL	VARCHAR		file types in use: `job` `job_chain` `order` file types not used: `process_class` `config` `lock` `schedule` `...`
`FILE_NAME`	NOT NULL	VARCHAR		path from live directory e.g. `test/my_job.job.xml`
`FILE_BASENAME`	NOT NULL	VARCHAR		file base name e.g. `my_job`
`FILE_DIRECTORY`	NOT NULL	VARCHAR		path calculated from the `live` directory e.g. `test`
`FILE_CREATED`	NULL	DATETIME		UTC timestamp of the creation date of the file
`FILE_MODIFIED`	NULL	DATETIME		UTC timestamp of the modification date of the file
`FILE_LOCAL_CREATED`	NULL	DATETIME		local timestamp of the creation date of the file
`FILE_LOCAL_MODIFIED`	NULL	DATETIME		local timestamp of the modification date of the file
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

INVENTORY_JOBS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_INSTANCES.ID` used for simplified clean-up
`FILE_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_FILES.ID`
`NAME`	NOT NULL	VARCHAR		job name e.g. `test/my_job` for a given job path`test/my_job.job.xml`
`BASENAME`	NOT NULL	VARCHAR		job base name e.g. `my_job` for a given job path `test/my_job.job.xml`
`TITLE`	NULL	VARCHAR		job title
`IS_ORDER_JOB`	NOT NULL	NUMERIC		specifies if this job is part of a job chain: `1: yes` `0: no`
`IS_RUNTIME_DEFINED`	NOT NULL	NUMERIC		specifies if a start time has been configured: `1: yes` `0: no`
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

INVENTORY_JOB_CHAINS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_INSTANCES.ID` used for simplified clean-up
`FILE_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_FILES.ID`
`START_CAUSE`	NOT NULL	VARCHAR		the following start causes are available: `file_trigger` `order` TODO complete the list of start causes
`NAME`	NOT NULL	VARCHAR		job chain name e.g. `test/my_jobchain` for a given job chain path`test/my_jobchain.job_chain.xml`
`BASENAME`	NOT NULL	VARCHAR		job chain base name e.g. `my_jobchain` for a given job chain path `test/my_jobchain.job_chain.xml`
`TITLE`	NULL	VARCHAR		job chain title
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

INVENTORY_JOB_CHAIN_NODES

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_INSTANCES.ID` used for simplified clean-up
`JOB_CHAIN_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_JOB_CHAINS.ID`
`NAME`	NOT NULL	VARCHAR		job node name: `file_order_source` `job_chain_node` `file_order_sink`
`ORDERING`	NOT NULL	NUMERIC		ordering of node in job chain
`STATE`	NULL	VARCHAR		job node state as specified in the configuration
`NEXT_STATE`	NULL	VARCHAR		next state for an order in case of successful execution
`ERROR_STATE`	NULL	VARCHAR		error state for an order in case of unsuccessful execution
`JOB`	NULL	VARCHAR		job name as specified in the job node configuration e.g. `../my_job`
`JOB_NAME`	NOT NULL	VARCHAR	Foreign Key	corresponds to `INVENTORY_JOBS.NAME.` Paths are resolved to absolute values starting from the `live` directory e.g. `test/my_job`
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

INVENTORY_JOB_CHAIN_ORDERS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`INSTANCE_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_INSTANCES.ID` used for simplified clean-up
`FILE_ID`	NOT NULL	NUMERIC	Foreign Key	references `INVENTORY_FILES.ID`
`JOB_CHAIN_NAME`	NOT NULL	VARCHAR	Foreign Key	corresponds to `INVENTORY_JOB_CHAINS.NAME` e.g. `test/my_jobchain`
`ORDER_ID`	NOT NULL	VARCHAR		order identification (unique per job chain) e.g. `my_order` for a given order path`test/my_jobchain,my_order.order.xml`
`TITLE`	NULL	VARCHAR		order title
`IS_RUNTIME_DEFINED`	NOT NULL	NUMERIC		specifies if a start time has been configured: `1: yes` `0: no`
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

Fact Tables

The following tables are used to collect facts from the JobScheduler history.
No standalone jobs are given. Fact tables contain job chain starts exclusively.
These tables can be used to create individual reports.
SQL Scripts
- Tables: report.sql

REPORT_TRIGGERS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	VARCHAR		corresponds to `SCHEDULER_ORDER_HISTORY.SCHEDULER_ID`
`HISTORY_ID`	NOT NULL	NUMERIC		corresponds to `SCHEDULER_ORDER_HISTORY.HISTORY_ID`
`NAME`	NOT NULL	VARCHAR		corresponds to `SCHEDULER_ORDER_HISTORY.ORDER_ID`
`TITLE`	NULL	VARCHAR		order title corresponds to `INVENTORY_JOB_CHAIN_ORDERS.TITLE`
`PARENT_NAME`	NOT NULL	VARCHAR		corresponds to `SCHEDULER_ORDER_HISTORY.JOB_CHAIN`
`PARENT_BASENAME`	NULL	VARCHAR		job chain base name
`PARENT_TITLE`	NULL	VARCHAR		corresponds to `INVENTORY_JOB_CHAINS.TITLE`
`START_TIME`	NOT NULL	DATETIME		corresponds to `SCHEDULER_ORDER_HISTORY.START_TIME`
`END_TIME`	NULL	DATETIME		corresponds to `SCHEDULER_ORDER_HISTORY.END_TIME`
`SYNC_COMPLETED`	NOT NULL	NUMERIC		for use by the JobScheduler Reporting Interface jobs only: specifies if a synchronization has been configured and has been completed successfully: `1: yes` `0: no`
`RESULTS_COMPLETED`	NOT NULL	NUMERIC		for use by the JobScheduler Reporting Interface jobs only: specifies if all job nodes have been completed successfully: `1: yes` `0: no`
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

REPORT_EXECUTIONS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	NUMERIC		corresponds to `SCHEDULER_ORDER_HISTORY.SCHEDULER_ID`
`HISTORY_ID`	NOT NULL	NUMERIC		corresponds to `SCHEDULER_ORDER_HISTORY.HISTORY_ID`
`TRIGGER_ID`	NOT NULL	NUMERIC	Foreign Key	references `REPORT_TRIGGERS.ID`
`STEP`	NOT NULL	NUMERIC		corresponds to `SCHEDULER_ORDER_STEP_HISTORY.STEP`
`NAME`	NOT NULL	VARCHAR		corresponds to `SCHEDULER_HISTORY.JOB_NAME` e.g. `test/my_job`
`BASENAME`	NOT NULL	VARCHAR		job base name e.g. `my_job`, for a given job path `test/my_job.job.xml`
`TITLE`	NULL	VARCHAR		corresponds to `INVENTORY_JOBS.TITLE`
`START_TIME`	NOT NULL	DATETIME		corresponds to `SCHEDULER_ORDER_STEP_HISTORY.START_TIME`
`END_TIME`	NULL	DATETIME		corresponds to `SCHEDULER_ORDER_STEP_HISTORY.END_TIME`
`STATE`	NOT NULL	VARCHAR		corresponds to `SCHEDULER_ORDER_STEP_HISTORY.STATE`
`CAUSE`	NOT NULL	VARCHAR		start cause for execution, can be mapped to an individual start cause by use of table `REPORT_MAP_CAUSES`
`ERROR`	NOT NULL	NUMERIC		specifies if an error occurred: `0: no error` `1: error`
`ERROR_CODE`	NULL	VARCHAR		JobScheduler error code
`ERROR_TEXT`	NULL	VARCHAR		error message
`IS_RUNTIME_DEFINED`	NOT NULL	NUMERIC		specifies if a start time has been configured: `1: yes` `0: no`
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

Aggregation Tables

The following tables contain aggregations for
- job steps per order
- triggers and executions per day, week, month, quarter, year
SQL Scripts
- Tables: report.sql

REPORT_TRIGGER_RESULTS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	VARCHAR		corresponds to `SCHEDULER_ORDER_HISTORY.SCHEDULER_ID`
`HISTORY_ID`	NOT NULL	NUMERIC		corresponds to `SCHEDULER_ORDER_HISTORY.HISTORY_ID`
`TRIGGER_ID`	NOT NULL	NUMERIC	Foreign Key	references REPORT_TRIGGERS.ID
`START_CAUSE`	NOT NULL	VARCHAR		references `SCHEDULER_HISTORY.CAUSE` of the first step for the value `order` check the start cause given in `INVENTORY_JOB_CHAINS.START_CAUSE`
`STEPS`	NOT NULL	NUMERIC		specifies the number of job steps that have been executed
`ERROR`	NOT NULL	NUMERIC		specifies if an error occurred `0: no error` `1: error`
`ERROR_CODE`	NULL	VARCHAR		JobScheduler error code
`ERROR_TEXT`	NULL	VARCHAR		error message
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

REPORT_EXECUTION_DATES

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	VARCHAR		corresponds to `SCHEDULER_ORDER_HISTORY.SCHEDULER_ID`
`HISTORY_ID`	NOT NULL	NUMERIC		corresponds to `SCHEDULER_ORDER_HISTORY.HISTORY_ID`
`REFERENCE_ID`	NOT NULL	NUMERIC	Foreign Key	references `REPORT_TRIGGERS.ID` or `REPORT_EXECUTIONS.ID`
`REFERENCE_TYPE`	NOT NULL	NUMERIC		type of reference: `0:` references `REPORT_TRIGGERS` `1:` references `REPORT_EXECUTIONS`
`START_DAY`	NOT NULL	NUMERIC		calendar day e.g. 1
`START_WEEK`	NOT NULL	NUMERIC		calendar week e.g. 52
`START_MONTH`	NOT NULL	NUMERIC		calendar month e.g. 12
`START_QUARTER`	NOT NULL	NUMERIC		quarter e.g. 1
`START_YEAR`	NOT NULL	NUMERIC		year e.g. 2015
`END_DAY`	NOT NULL	NUMERIC		calendar day e.g. 1
`END_WEEK`	NOT NULL	NUMERIC		calendar week e.g. 52
`END_MONTH`	NOT NULL	NUMERIC		calendar month e.g. 12
`END_QUARTER`	NOT NULL	NUMERIC		quarter e.g. 1
`END_YEAR`	NOT NULL	NUMERIC		calendar year e.g. 2015
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

Mapping Tables

The following tables are used for the mapping of report data. Users can add individual records that are used for the mapping of results in individual reports:
- Mapping of start causes for jobs
Contents in these tables is optional, however, their existence is required.
SQL Scripts
- Tables: report.sql
- Sample Data: report_map_insert.sql

REPORT_MAP_CAUSES

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`CAUSE`	NOT NULL	VARCHAR		start cause as used by JobScheduler: possible values include `order`: job start triggered by manual order `min_tasks`: job start triggered by forced minimum number of tasks `delay_after_error`: job start due to a setback event `period_single`: job start triggered by order start time `period_repeat`: job start triggered by order repeat interval `queue_at`: job start scheduled manually or by task congestion additional values provided by the JobScheduler Reporting Interface include `file_trigger`: job start due to an incoming file
`MAPPED_CAUSE`	NOT NULL	VARCHAR		individual mapping of start cause
`CREATED`	NOT NULL	DATETIME		timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		timestamp of record modification

Standard Procedures

For most DBMS it is recommended to use views or procedures in order to speed up queries by pre-compiled statements.
The following procedures implement the use of the standard tables for reporting results.
SQL Scripts
- Procedures: report_procedure.sql

Report about Installed Objects

Report about jobs, job chains and orders as collected from disk.

REPORT_INSTALLED_OBJECTS

CREATE OR REPLACE FUNCTION REPORT_INSTALLED_OBJECTS(start_date DATE, end_date DATE) RETURNS TABLE (
    "ID"                    BIGINT,
    "SCHEDULER_ID"          VARCHAR(100),
    "HOSTNAME"              VARCHAR(255),
    "JOB_CHAIN"             VARCHAR(255), 
    "JOB_NAME"              VARCHAR(255), 
    "JOB_BASENAME"          VARCHAR(100),
    "JOB_CHAIN_BASENAME"    VARCHAR(100),
    "JOB_TITLE"             VARCHAR(255),
    "JOB_CHAIN_TITLE"       VARCHAR(255),
    "CAUSE"                 VARCHAR(100),
    "IS_ACTIVE_CALENDAR"    INT,
    "FILE_CREATED"          TIMESTAMP,
    "FILE_MODIFIED"         TIMESTAMP
)

Report about Execution Summary

Report about executions of jobs and job chains.

REPORT_EXECUTION_SUMMARY

CREATE OR REPLACE FUNCTION REPORT_EXECUTION_SUMMARY(start_date DATE, end_date DATE) RETURNS TABLE ( 
    "ID"                    BIGINT,
    "SCHEDULER_ID"          VARCHAR(100),
    "HOSTNAME"              VARCHAR(255), 
    "JOB_CHAIN"             VARCHAR(255), 
    "JOB_NAME"              VARCHAR(255), 
    "JOB_BASENAME"          VARCHAR(100),
    "JOB_CHAIN_BASENAME"    VARCHAR(100),
    "JOB_TITLE"             VARCHAR(255),
    "JOB_CHAIN_TITLE"       VARCHAR(255),
    "CAUSE"                 VARCHAR(100),
    "IS_ACTIVE_CALENDAR"    INT,
    "START_TIME"            TIMESTAMP,
    "END_TIME"              TIMESTAMP,
    "START_DATE"            DATE,
    "END_DATE"              DATE
)

Customization

The standard data model can be customized by use of additional tables and procedures.
Such tables are not required by the JobScheduler Reporting Interface.

Sample Custom Tables

The following tables implement samples for the customization of reports:
- Assigning applications to jobs
- Assigning mandators to jobs
Samples are presented for instructional use only.
SQL Scripts
- Tables: report_custom.sql
- Sample Data: report_custom_insert.sql

Assigning Applications to Jobs

Should a report present information on a number of jobs that are used for the same application then the following tables could be used:

Application Master Data

REPORT_CUSTOM_APPS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`NAME`	NOT NULL	VARCHAR		application name
`TITLE`	NULL	VARCHAR		application title

Application Joins to Jobs

REPORT_CUSTOM_JOB_APPS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`JOB_NAME`	NOT NULL	VARCHAR		`corresponds to INVENTORY_JOBS.NAME`
`APP_NAME`	NOT NULL	VARCHAR		corresponds `REPORT_CUSTOM_APPS.NAME`

Assigning Mandators to Jobs

Mandator Master Data

REPORT_CUSTOM_MANDATORS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`NAME`	NOT NULL	VARCHAR		mandator name
`TITLE`	NULL	VARCHAR		mandator title

Mandator Joins to Jobs

REPORT_CUSTOM_JOB_MANDATORS

Field	Nullable	Data type	Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`JOB_CHAIN`	NOT NULL	VARCHAR	Foreign Key	references `REPORT_JOBS.ID`
`MANDATOR_ID`	NOT NULL	NUMERIC	Foreign Key	references `REPORT_MANDATOR.ID`

Sample Custom Procedures

For most DBMS it is recommended to use views or procedures in order to speed up queries by pre-compiled statements.
The following procedures implement the use of the above custom tables for reporting results that are aggregated per application and mandator.
SQL Scripts
- Procedures: report_custom_procedure.sql

Report about Installed Objects

The standard procedure is improved to map installed objects to applications and mandators.

Custom Report on Installed Objects

REPORT_CUSTOM_INSTALLED_OBJECTS

CREATE OR REPLACE FUNCTION REPORT_CUSTOM_INSTALLED_OBJECTS(start_date DATE, end_date DATE) RETURNS TABLE (
    "ID"                    BIGINT,
    "SCHEDULER_ID"          VARCHAR(100),
    "HOSTNAME"              VARCHAR(255),
    "JOB_CHAIN"             VARCHAR(255), 
    "JOB_NAME"              VARCHAR(255), 
    "JOB_BASENAME"          VARCHAR(100),
    "JOB_CHAIN_BASENAME"    VARCHAR(100),
    "JOB_TITLE"             VARCHAR(255),
    "JOB_CHAIN_TITLE"       VARCHAR(255),
    "CAUSE"                 VARCHAR(100),
    "IS_ACTIVE_CALENDAR"    INT,
    "FILE_CREATED"          DATE,
    "FILE_MODIFIED"         DATE,
    "MANDATOR_NAME"         VARCHAR(100), 
    "APPLICATION_NAME"      VARCHAR(100)
)

Report about Executions

Custom Report on Execution Summary

REPORT_CUSTOM_EXECUTION_SUMMARY

CREATE OR REPLACE FUNCTION REPORT_CUSTOM_EXECUTION_SUMMARY(start_date DATE, end_date DATE) RETURNS TABLE (

    "ID"                    BIGINT,
    "SCHEDULER_ID"          VARCHAR(100),
    "HOSTNAME"              VARCHAR(255), 
    "JOB_CHAIN"             VARCHAR(255), 
    "JOB_NAME"              VARCHAR(255), 
    "JOB_BASENAME"          VARCHAR(100),
    "JOB_CHAIN_BASENAME"    VARCHAR(100),
    "JOB_TITLE"             VARCHAR(255),
    "JOB_CHAIN_TITLE"       VARCHAR(255),
    "CAUSE"                 VARCHAR(100),
    "IS_ACTIVE_CALENDAR"    INT,
    "START_TIME"            TIMESTAMP,
    "END_TIME"              TIMESTAMP,
    "START_DATE"            DATE,
    "END_DATE"              DATE,
    "MIN_START_DATE"        DATE,
    "MAX_START_DATE"        DATE,
    "MANDATOR_NAME"         VARCHAR(100), 
    "APPLICATION_NAME"      VARCHAR(100)
)

Space shortcuts

Page tree

Scope

Standard Data Model

Standard Tables

Inventory Tables

Fact Tables

Aggregation Tables

Mapping Tables

Standard Procedures

Report about Installed Objects

Report about Execution Summary

Customization

Sample Custom Tables

Assigning Applications to Jobs

Application Master Data

Application Joins to Jobs

Assigning Mandators to Jobs

Mandator Master Data

Mandator Joins to Jobs

Sample Custom Procedures

Report about Installed Objects

Custom Report on Installed Objects

Report about Executions

Custom Report on Execution Summary

Space shortcuts

Page tree

JobScheduler Reporting Interface - Technical Information - Data Model

Scope

Standard Data Model

Standard Tables

Inventory Tables

Fact Tables

Aggregation Tables

Mapping Tables

Standard Procedures

Report about Installed Objects

Report about Execution Summary

Customization

Sample Custom Tables

Assigning Applications to Jobs

Application Master Data

Application Joins to Jobs

Assigning Mandators to Jobs

Mandator Master Data

Mandator Joins to Jobs

Sample Custom Procedures

Report about Installed Objects

Custom Report on Installed Objects

Report about Executions

Custom Report on Execution Summary