Product Knowledge Base

Space shortcuts

Page tree

Versions Compared

Old Version 7

changes.mady.by.user Andreas Püschel

Saved on

compared with

New Version 8

changes.mady.by.user Andreas Püschel

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Provide technical information on the data model.
  • Users can use this information to create their own reports from the data model.

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

 

Expand
titleINVENTORY_INSTANCES
FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
SCHEDULER_IDNOT NULLVARCHAR corresponds to the SCHEDULER_ID that is specified during installation of the JobScheduler instance
HOSTNAMENOT NULLVARCHAR host on which the JobScheduler instance has been installed
PORTNOT NULLNUMERIC 

TCP port that the JobScheduler instance is listening to. Should not TCP port be specified then the UDP port is provided.

LIVE_DIRECTORYNOT NULLVARCHAR path of the live directory
CREATEDNOT NULLDATETIME timestamp of record creation
MODIFIEDNOT NULLDATETIME timestamp of record modification

...

Expand
titleINVENTORY_JOB_CHAIN_ORDERS
FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
INSTANCE_IDNOT NULLNUMERICForeign Key

referencesINVENTORY_INSTANCES.ID

used for simplified clean-up
FILE_IDNOT NULLNUMERICForeign Keyreferences INVENTORY_FILES.ID
JOB_CHAIN_NAMENOT NULLVARCHARForeign Key

corresponds to INVENTORY_JOB_CHAINS.NAME

e.g. test/my_jobchain

ORDER_IDNOT NULLVARCHAR 

order identification (unique per job chain)

e.g. my_order for a given order path test/my_jobchain,my_order.order.xml

TITLENULLVARCHAR order title

IS_RUNTIME_DEFINED

 

NOT NULLNUMERIC 

specifies if a start time has been configured:

  • 1: yes
  • 0: no
CREATEDNOT NULLDATETIME timestamp of record creation
MODIFIEDNOT NULLDATETIME 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

 

Expand
titleREPORT_TRIGGERS
FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 

SCHEDULER_ID

NOT NULL

VARCHAR

 

corresponds to SCHEDULER_ORDER_HISTORY.SCHEDULER_ID

HISTORY_IDNOT NULLNUMERIC 

corresponds to SCHEDULER_ORDER_HISTORY.HISTORY_ID

NAMENOT NULLVARCHAR 

corresponds to SCHEDULER_ORDER_HISTORY.ORDER_ID

TITLENULLVARCHAR 

order title

corresponds to INVENTORY_JOB_CHAIN_ORDERS.TITLE

PARENT_NAMENOT NULLVARCHAR 

corresponds to SCHEDULER_ORDER_HISTORY.JOB_CHAIN

PARENT_BASENAMENULLVARCHAR job chain base name
PARENT_TITLENULLVARCHAR 

corresponds to INVENTORY_JOB_CHAINS.TITLE

START_TIMENOT NULLDATETIME 

corresponds to SCHEDULER_ORDER_HISTORY.START_TIME

END_TIMENULLDATETIME 

corresponds to SCHEDULER_ORDER_HISTORY.END_TIME

SYNC_COMPLETEDNOT NULLNUMERIC 

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_COMPLETEDNOT NULLNUMERIC 

for use by the JobScheduler Reporting Interface jobs only:
specifies if all job nodes have been completed successfully:

  • 1: yes
  • 0: no
CREATEDNOT NULLDATETIME timestamp of record creation
MODIFIEDNOT NULLDATETIME timestamp of record modification
Expand
titleREPORT_EXECUTIONS
FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
SCHEDULER_ID NOT NULLNUMERIC corresponds to SCHEDULER_ORDER_HISTORY.SCHEDULER_ID
HISTORY_IDNOT NULLNUMERIC corresponds to SCHEDULER_ORDER_HISTORY.HISTORY_ID
TRIGGER_IDNOT NULLNUMERICForeign Keyreferences REPORT_TRIGGERS.ID
STEPNOT NULLNUMERIC 

corresponds to SCHEDULER_ORDER_STEP_HISTORY.STEP

NAMENOT NULLVARCHAR 

corresponds to SCHEDULER_HISTORY.JOB_NAME

e.g. test/my_job

BASENAMENOT NULLVARCHAR 

job base name

e.g. my_job, for a given job path test/my_job.job.xml

TITLENULLVARCHAR corresponds to INVENTORY_JOBS.TITLE
START_TIMENOT NULLDATETIME 

corresponds to SCHEDULER_ORDER_STEP_HISTORY.START_TIME

END_TIMENULLDATETIME 

corresponds to SCHEDULER_ORDER_STEP_HISTORY.END_TIME

STATENOT NULLVARCHAR 

corresponds to SCHEDULER_ORDER_STEP_HISTORY.STATE

CAUSENOT NULLVARCHAR start cause for execution, can be mapped to an individual start cause by use of table REPORT_MAP_CAUSES
ERRORNOT NULLNUMERIC 

specifies if an error occurred:

  • 0: no error
  • 1: error
ERROR_CODENULLVARCHAR JobScheduler error code
ERROR_TEXTNULLVARCHAR error message
IS_RUNTIME_DEFINEDNOT NULLNUMERIC 

specifies if a start time has been configured:

  • 1: yes
  • 0: no
CREATEDNOT NULLDATETIME timestamp of record creation
MODIFIEDNOT NULLDATETIME 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

 

Expand
titleREPORT_TRIGGER_RESULTS
FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
SCHEDULER_IDNOT NULLVARCHAR corresponds to SCHEDULER_ORDER_HISTORY.SCHEDULER_ID
HISTORY_IDNOT NULLNUMERIC corresponds to SCHEDULER_ORDER_HISTORY.HISTORY_ID
TRIGGER_IDNOT NULLNUMERICForeign Key

references REPORT_TRIGGERS.ID

START_CAUSENOT NULLVARCHAR 

references SCHEDULER_HISTORY.CAUSE of the first step

for the value order check the start cause given in INVENTORY_JOB_CHAINS.START_CAUSE

STEPSNOT NULLNUMERIC 

specifies the number of job steps that have been executed

ERRORNOT NULLNUMERIC 

specifies if an error occurred

  • 0: no error
  • 1: error
ERROR_CODENULLVARCHAR JobScheduler error code
ERROR_TEXTNULLVARCHAR error message
CREATEDNOT NULLDATETIME timestamp of record creation
MODIFIEDNOT NULLDATETIME timestamp of record modification
Expand
titleREPORT_EXECUTION_DATES
FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
SCHEDULER_IDNOT NULLVARCHAR corresponds to SCHEDULER_ORDER_HISTORY.SCHEDULER_ID
HISTORY_IDNOT NULLNUMERIC corresponds to SCHEDULER_ORDER_HISTORY.HISTORY_ID
REFERENCE_IDNOT NULLNUMERICForeign Key

references REPORT_TRIGGERS.ID or REPORT_EXECUTIONS.ID

REFERENCE_TYPENOT NULLNUMERIC 

type of reference:

  • 0: references REPORT_TRIGGERS
  • 1: references REPORT_EXECUTIONS
START_DAYNOT NULLNUMERIC 

calendar day

e.g. 1

START_WEEKNOT NULLNUMERIC 

calendar week

e.g. 52

START_MONTHNOT NULLNUMERIC 

calendar month

e.g. 12

START_QUARTERNOT NULLNUMERIC 

quarter

e.g. 1

START_YEARNOT NULLNUMERIC 

year

e.g. 2015

END_DAYNOT NULLNUMERIC 

calendar day

e.g. 1

END_WEEKNOT NULLNUMERIC 

calendar week

e.g. 52

END_MONTHNOT NULLNUMERIC 

calendar month

e.g. 12

END_QUARTERNOT NULLNUMERIC 

quarter

e.g. 1

END_YEARNOT NULLNUMERIC 

calendar year

e.g. 2015

CREATEDNOT NULLDATETIME timestamp of record creation
MODIFIEDNOT NULLDATETIME 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

 

Expand
titleREPORT_MAP_CAUSES
FieldNullableData typeConstraintDescription
IDNOT NULLNUMERICPrimary Key 
CAUSENOT NULLVARCHAR 

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_CAUSENOT NULLVARCHAR individual mapping of start cause
CREATEDNOT NULLDATETIME timestamp of record creation
MODIFIEDNOT NULLDATETIME timestamp of record modification

Customization

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

Report about Installed Objects

  • Report about jobs, job chains and orders as collected from disk.
Expand
titleREPORT_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
titleREPORT_INSTALLED_OBJECTS

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 The standard data model can be customized by use of additional tables and procedures.
  • Such tables are not required by the JobScheduler Reporting Interface.

...

  • 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

Assigning Applications to Jobs

...

  • 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

Report about Installed Objects

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

...