Page History

...

Expand

title	REPORTING_TASKS

Field	Nullable	Data type	Constraint	Unique Constraint	Description
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	NUMERIC		X	corresponds to `SCHEDULER_HISTORY.SPOOLER_ID`
`HISTORY_ID`	NOT NULL	NUMERIC		X	corresponds to `SCHEDULER_HISTORY.ID`
IS_ORDER	NOT NULL	NUMERIC			cause for execution of the task: `1: order task` `0: standalone task`
CLUSTER_MEMBER_ID		VARCHAR			corresponds to `SCHEDULER_HISTORY.CLUSTER_MEMBER_ID`
`STEPS`	NOT NULL	NUMERIC			number of steps --> calls to spooler_process() corresponds to `SCHEDULER_HISTORY.STEPS`
FOLDER	NOT NULL	VARCHAR			job folder e.g. `/test`, for a given job path `/test/my_job.job.xml`
`NAME`	NOT NULL	VARCHAR			job name corresponds to `SCHEDULER_HISTORY.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 corresponds to `INVENTORY_JOBS.TITLE`
`START_TIME`	NOT NULL	DATETIME			UTC timestamp of the task start corresponds to `SCHEDULER_HISTORY.START_TIME`
`END_TIME`	NULL	DATETIME			UTC timestamp of the task end corresponds to `SCHEDULER_HISTORY.END_TIME`
`CAUSE`	NOT NULL	VARCHAR			cause of the task start e.g. : none period_once period_single period_repeat job_repeat queue queue_at directory signal delay_after_error corresponds to `SCHEDULER_HISTORY.CAUSE`
EXIT_	corresponds to `SCHEDULER_HISTORY.CAUSE` start cause for execution, can be mapped to an individual start cause by use of table `REPORT_MAP_CAUSES`	EXIT_CODE	NOT NULL	NUMERIC		corresponds to `SCHEDULER_HISTORY.EXIT_CODE`
`ERROR`	NOT NULL	NUMERIC			specifies if an error occurred: `0: no error` `1: error`
`ERROR_CODE`	NULL	VARCHAR			JobScheduler exception-code of the task error code
`ERROR_TEXT`	NULL	VARCHAR			exception-message of the task error message
AGENT_URL		VARCHAR			agen url corresponds to `SCHEDULER_HISTORY.AGENT_URL`
`IS_RUNTIME_DEFINED`	NOT NULL	NUMERIC			specifies if a start time has been configured: `1: yes` `0: no` corresponds to `INVENTORY_JOBS.IS_RUNTIME_DEFINED`
SYNC_COMPLETED	NOT NULL	NUMERIC			for use by the JobScheduler Reporting Interface only: specifies if a synchronization has been completed: `1: yes` `0: no`
RESULTS_COMPLETED	NOT NULL	NUMERIC			for use by the JobScheduler Reporting Interface only: specifies if a aggregation has been completed: `1: yes` `0: no`
`CREATED`	NOT NULL	DATETIME			UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME			UTC timestamp of record modification

...

The following tables contain aggregations forjob steps per order

triggers, executions and

executions per

tasks per day, week, month, quarter, year

SQL Scripts

Tables: report.sql

Expand

title	REPORTREPORTING_TRIGGEREXECUTION_RESULTSDATES

VARCHARSTEPSERROR

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`
`TRIGGERREFERENCE_ID`	NOT NULL	NUMERIC	Foreign Key	references REPORT`REPORTING_TRIGGERS.ID` `START_CAUSE` or `REPORTING_EXECUTIONS.ID or REPORTIN_TASKS.ID`
`REFERENCE_TYPE`	NOT NULL	NUMERIC		references `SCHEDULER_HISTORY.CAUSE` of the first step for the value `order` check the start cause given in `INVENTORY_JOB_CHAINS.START_CAUSE`	type of reference: `0:` references `REPORTING_TRIGGERS` `1:` references `REPORTING_EXECUTIONS` `2: references REPORTING_TASKS`
`START_DAY`		NOT NULL	NUMERIC		specifies the number of job steps that have been executed	calendar day e.g. 1
`START_WEEK`		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

Expand

title	REPORT_EXECUTION_DATES

HISTORY_IDREFERENCE_TYPENUMERIC

Field	Nullable	Data type	Constraint	Description
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`
`ID`	NOT NULL	NUMERIC	Primary Key
`SCHEDULER_ID`	NOT NULL	VARCHAR		corresponds to `SCHEDULER_ORDER_HISTORY.SCHEDULER_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`
calendar year e.g. 2015
`CREATED`		NOT NULL	DATETIME		type of reference: `0:` references `REPORT_TRIGGERS` `1:` references `REPORT_EXECUTIONS`	UTC timestamp of record creation
`MODIFIED`	`START_DAY`	NOT NULL	NUMERICDATETIME		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
UTC 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

Sample Data: reporting_map_insert.sql

Expand

title	REPORTING_MAP_CAUSES

Field	Nullable	Data type	Constraint	Description
`IDSTART_QUARTER`	NOT NULL	NUMERIC	Primary Key		quarter e.g. 1	`START_YEAR`
`CAUSE`	NOT NULL	NUMERICVARCHAR		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
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		UTC timestamp of record creation
`MODIFIED`	NOT NULL	DATETIME		UTC 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

Expand

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

Expand

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

Expand

title	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
)

...

Standard Procedures

Cleanup

SQL Scripts : reporting_cleanup_procedure.sql

Report

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 : reporting_procedure.sql

Report about Installed Objects

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

Report about Execution Summary

Report about executions of jobs and job chains.

Space shortcuts

Page tree

Versions Compared

Old Version 17

New Version 18

Key

Mapping Tables

Mapping Tables

Standard Procedures

Report about Installed Objects

Report about Execution Summary

Standard Procedures

Cleanup

Report

Report about Installed Objects

Report about Execution Summary