• Skip to main content
• assistive.skiplink.to.breadcrumbs
• assistive.skiplink.to.header.menu
• assistive.skiplink.to.action.menu
• assistive.skiplink.to.quick.search

Log in Skip to sidebar Skip to main content

Product Knowledge Base

Space shortcuts

• Company Web Site
• Change Management System
• JS7 JobScheduler

Page tree

Browse pages

ConfigureSpace tools

• View Page
• • Attachments (11)
  • Page History

  • Page Information
  • View in Hierarchy
  • View Source
  • Export to PDF
  • Export to Word

  • Page Labels
  • Attachment Labels

Page History

Versions Compared

Old Version 1

changes.mady.by.user Robert Ehrlich

Saved on Apr 19, 2017

compared with

New Version Current

changes.mady.by.user Andreas Püschel

Saved on May 21, 2020

• View Page History

Key

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

...

• Provide technical information on the standard data model that is available for reports.
• Users can use this information to create their own reports from the data model, see JobScheduler Reporting Interface (1.11.x) - Technical Information - Data Model Customization
• SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

...

• 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 ScriptsTables:   inventory.sql

  ...

  • • SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

  
  

  SCHEDULER

  Expand
  title INVENTORY_AGENT_INSTANCESCLUSTERMEMBERS
  Field Nullable Data type Constraint Unique Constraint  Description ID NOT NULL NUMERIC Primary Key

  INSTANCE_ID	NOT NULL

  VARCHAR corresponds to the SCHEDULER_ID that is specified during installation of the JobScheduler instanceHOSTNAME

  NUMERIC	Foreign Key	X	references INVENTORY_INSTANCES.ID
  AGENT_CLUSTER_ID	NOT NULL

  VARCHAR host on which the JobScheduler instance has been installedPORT

  NUMERIC	Foreign Key	X	references INVENTORY_AGENT_CLUSTERS.ID
  AGENT_INSTANCE_ID	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

  Foreign Key	X	references INVENTORY_AGENT_INSTANCES.ID
  URL	NOT NULL	VARCHAR

   path of the live directory

  		agent url references INVENTORY_AGENT_INSTANCES.URL
  ORDERING	NOT NULL	NUMERIC			Order of agents in remote_schedulers element
  CREATED	NOT NULL	DATETIME

   

  		UTC timestamp of record creation
  MODIFIED	NOT NULL	DATETIME

   

  UTC timestamp of record modification

local timestamp of the modification date of the file

Expand
title INVENTORY_FILESAGENT_CLUSTERS
Field Nullable Data type Constraint Unique Constraint  Description ID NOT NULL NUMERIC Primary Key   INSTANCE_ID NOT NULL NUMERIC Foreign Key references INVENTORY_INSTANCES.ID FILE PROCESS_CLASS_ TYPE ID NOT NULL VARCHAR NUMERIC		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 with extension e.g. my_job.job.xml
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

Foreign Key	X	references INVENTORY_PROCESS_CLASSES.ID
SCHEDULING_TYPE	NOT NULL	VARCHAR			type of scheduling configured for the agent cluster first Fixed Priority Scheduling next Round Robin Scheduling single
NUMBER_OF_AGENTS	NOT NULL	NUMERIC
CREATED	NOT NULL	DATETIME			UTC timestamp of record creation
MODIFIED	NOT NULL	DATETIME			UTC timestamp of record modification

Expand
title INVENTORY_APPLIED_LOCKS
Field Nullable Data type Constraint Unique Constraint  Description ID NOT NULL NUMERIC Primary Key JOB_ID NOT NULL NUMERIC Foreign Key   X references INVENTORY_JOBS.ID LOCK_ID NOT NULL NUMERIC Foreign Key  X references INVENTORY_LOCKS.ID CREATED NOT NULL DATETIME

		UTC timestamp of record creation
MODIFIED	NOT NULL	DATETIME

 

UTC timestamp of record modification

  

Expand
title INVENTORY_JOBSFILES
Field Nullable Data type Constraint Unique Constraint Description ID NOT NULL NUMERIC Primary Key   INSTANCE_ID NOT NULL NUMERIC Foreign Key  X references INVENTORY_INSTANCES.ID used for simplified clean-up FILE_ ID TYPE NOT NULL	NUMERIC	Foreign Key	references INVENTORY_FILES.ID

	VARCHAR			file types in use: job job_chain order file types not used: process_class config lock schedule ...
FILE_NAME	NOT NULL	VARCHAR

 

X

path from live directory

job name

e.g.

test

/

my_job for a given job path

test/my_job.job.xml
FILE_BASENAME	NOT NULL	VARCHAR

 job

file base name with extension e.g. my_job

for a given job path test/my_job

.job.xml

TITLE

FILE_DIRECTORY

NOT NULL

VARCHAR

 job title

IS_ORDER_JOB

NOT NULLNUMERIC 

specifies if this job is part of a job chain:

• 1: yes
• 0: no

IS_RUNTIME_DEFINED

 

NOT NULLNUMERIC 

specifies if a start time has been configured:

		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

1: yes

0: no

CREATED	NOT NULL	DATETIME

 

		UTC timestamp of record creation
MODIFIED	NOT NULL	DATETIME

 

UTC timestamp of record modification

  

INSTANCE

Expand
title INVENTORY_JOB_CHAINSINSTANCES
Field Nullable Data type Constraint Unique Constraint Description ID NOT NULL NUMERIC Primary Key

OS_

ID

ID

NOT

NULL

NULL

NUMERIC

NUMERIC

Foreign

Key

Key

references INVENTORY_

INSTANCES

OPERATING_SYSTEMS.ID

used for simplified clean-up

SCHEDULER

FILE

_ID

NOT NULL

NUMERIC

VARCHAR

Foreign Keyreferences INVENTORY_FILES.IDSTART_CAUSE

	X	corresponds to the SCHEDULER_ID that is specified during installation of the JobScheduler instance
HOSTNAME	NOT NULL	VARCHAR

 

X

the following start causes are available:

• file_trigger
• order

host on which the JobScheduler instance has been installed
PORT	NOT NULL	NUMERIC		X	HTTP port that the JobScheduler instance is listening to.
LIVE_DIRECTORY

NAME

NOT NULL

VARCHAR

 

job chain name

			path of the live directory
VERSION	NOT NULL	VARCHAR			JobScheduler version e.g.

test/my_jobchain for a given job chain path test/my_jobchain.job_chain.xml


1.11.0

COMMAND_URL

BASENAME

NOT NULL

VARCHAR

 

job chain base name

e.g. my_jobchain for a given job chain path test/my_jobchain.job_chain.xmlTITLECREATED

		HTTP URL for XML request response
URL	NOT NULL	VARCHAR

 job chain title

		TCP URL still used by supervisor
TIMEZONE

NOT NULL

DATETIME

VARCHAR

 timestamp of record creationMODIFIED

CLUSTER_TYPE	NOT NULL

DATETIMEFieldNullableData typeConstraintDescriptionIDNOT NULLNUMERICPrimary Key INSTANCE_IDNOT NULLNUMERICForeign Keyreferences

VARCHAR

 timestamp of record modification

Expand

title	INVENTORY_JOB_CHAIN_NODES

		possible values standalone active passive
PRECEDENCE		NUMERIC			precendence of JobScheduler in a passive cluster e.g. 0 (primary) , 1 (backup no. 1), 2 (backup no. 2) etc
DBMS_NAME	NOT NULL	VARCHAR			name of the database vendor e.g. MySql , Oracle, etc
DBMS_VERSION		VARCHAR			release number of database system
STARTED_AT		DATETIME
SUPERVISOR_ID		NUMERIC			reference

INVENTORY_INSTANCES.ID

used for simplified clean-up

AUTH		VARCHAR
CREATED

JOB_CHAIN_ID

NOT NULL

NUMERICForeign Keyreferences INVENTORY_JOB_CHAINS.IDNAMENOT NULLVARCHAR 

job node name:

• file_order_source
• job_chain_node
• file_order_sink

ORDERINGNOT NULLNUMERIC ordering of node in job chain

STATE

NULLVARCHAR job node state as specified in the configurationNEXT_STATENULLVARCHAR next state for an order in case of successful executionERROR_STATENULLVARCHAR error state for an order in case of unsuccessful executionJOBNULLVARCHAR 

job name as specified in the job node configuration

e.g. ../my_job

JOB_NAMENOT NULLVARCHARForeign Key

corresponds to INVENTORY_JOBS.NAME. Paths are resolved to absolute values starting from the live directory

e.g. test/my_job

CREATEDNOT NULLDATETIME timestamp of record creationMODIFIEDNOT NULLDATETIME timestamp of record modification

DATETIME			UTC timestamp of record creation
MODIFIED	NOT NULL	DATETIME			UTC timestamp of record modification

referencesINVENTORY_INSTANCES.IDused for simplified clean-up

Expand
title INVENTORY_JOB_CHAIN_NODES
Field Nullable Data type Constraint Unique Constraint Description ID NOT NULL NUMERIC Primary Key INSTANCE_ID NOT NULL NUMERIC Foreign Key references INVENTORY_INSTANCES.ID JOB_ID  NOT NULL  NUMERIC  Foreign Key  references INVENTORY_JOBS.ID  JOB_CHAIN_ID NOT NULL NUMERIC Foreign Key references INVENTORY_JOB_CHAINS.ID NESTED_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 VARCHAR job node state as specified in the configuration NEXT_STATE VARCHAR next state for an order in case of successful execution ERROR_STATE VARCHAR error state for an order in case of unsuccessful execution JOB VARCHAR job name as specified in the job node configuration e.g. ../my_job JOB_NAME NOT NULL VARCHAR corresponds to INVENTORY_JOBS.NAME. Paths are resolved to absolute values starting from the live directory e.g. /test/my_job NESTED_JOB_CHAIN VARCHAR  job_chain attribute of the node NESTED_JOB_CHAIN_NAME NOT NULL  VARCHAR  corresponds to INVENTORY_JOB_CHAINS.NAME  NODE_TYPE  NOT NULL  NUMERIC  specifies the node type: 1: job 2: job chain 3: file order source 4: file order sink 5: end node ON_ERROR  VARCHAR  on_error attribute of a job_chain_node DELAY  NUMERIC  delay attribute of a job_chain_node DIRECTORY  VARCHAR   directory attribute of a file_order_source REGEX  VARCHAR  regex attribute of a file_order_source FILE_SINK_OP  NUMERIC specifies the operation in case of file order sink : 1: move 2: remove MOVE_PATH  VARCHAR  in case of file order sink and operation move this column will have the directory path e.g. /data/input/archive CREATED NOT NULL DATETIME UTC
Expand
title INVENTORY_ORDERS
Field	Nullable	Data type	Constraint	Description
ID	NOT NULL	NUMERIC	Primary Key
INSTANCE_ID	NOT NULL	NUMERIC	Foreign Key
FILE_ID	NOT NULL	NUMERIC	Foreign Key	references INVENTORY_FILES.ID
NAME	NOT NULL	VARCHAR		order name e.g. test/my_jobchain,my_order for a given order path test/my_jobchain,my_order.order.xml
BASENAME	NOT NULL	VARCHAR		order base name e.g. my_jobchain,my_order for a given job chain path test/my_jobchain,my_order.order.xml
TITLE	NULL	VARCHAR		order title
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
JOB_CHAIN_NAME	NOT NULL	VARCHAR	Foreign Key	corresponds to INVENTORY_JOB_CHAINS.NAME e.g. test/my_jobchain
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

 

UTC 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

corresponds to SCHEDULER_ORDER_HISTORY.SCHEDULER_ID


PROCESS_CLASS

_ID

FW_PROCESS_CLASS

_ID

order title

corresponds to SCHEDULER_ORDER_HISTORY.JOB_CHAIN

corresponds to INVENTORY_JOB_CHAINS.TITLE

corresponds to SCHEDULER_ORDER_HISTORY.START_TIME

corresponds to SCHEDULER_ORDER_HISTORY.END_TIME

for use by the JobScheduler Reporting Interface jobs only:
specifies if a synchronization has been configured and has been completed successfully:

MAX_ORDERS

DISTRIBUTED

PROCESS_CLASS

PROCESS_CLASS_NAME

FW_PROCESS_CLASS

FW_PROCESS_CLASS_NAME

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

PROCESS_CLASS_ID

SCHEDULE_ID

corresponds to SCHEDULER_HISTORY.JOB_NAME

USED_IN_JOB_CHAINS

PROCESS_CLASS

PROCESS_CLASS_NAME

SCHEDULE

SCHEDULE_NAME

corresponds to SCHEDULER_ORDER_STEP_HISTORY.START_TIME

corresponds to SCHEDULER_ORDER_STEP_HISTORY.END_TIME

corresponds to SCHEDULER_ORDER_STEP_HISTORY.STATE

specifies if an error occurred:

• 0: no error
• 1: error

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

FILE_ID

NAME

references REPORT_TRIGGERS.ID

BASENAME

references SCHEDULER_HISTORY.CAUSE of the first step

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

specifies the number of job steps that have been executed

specifies if an error occurred

• 0: no error
• 1: error

MAX_NON_EXCLUSIVE

NAME

ARCHITECTURE

DISTRIBUTION

JOB_CHAIN_ID

SCHEDULE_

ID

type of reference:

• 0: references REPORT_TRIGGERS
• 1: references REPORT_EXECUTIONS

SCHEDULE

SCHEDULE_NAME

INITIAL_STATE

END_STATE

PRIORITY

calendar day

e.g. 1

calendar week

e.g. 52

calendar month

e.g. 12

quarter

e.g. 1

year

e.g. 2015

calendar day

e.g. 1

calendar week

e.g. 52

calendar month

e.g. 12

quarter

e.g. 1

calendar year

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

FILE_

ID

NAME

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

BASENAME

MAX_PROCESSES

HAS_AGENTS

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.

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.

...

...

FILE_ID

SUBSTITUTE_ID

NAME

BASENAME

TITLE

SUBSTITUTE

SUBSTITUTE_NAME

SUBSTITUTE_VALID_FROM

SUBSTITUTE_VALID_TO

Audit_log Table

• the following table is used to store information if a user takes actions with a specific Job Scheduler Object, e.g. starting/stopping of a JobScheduler instance, pausing a Job, modifying an order, etc..
• This table can be used to create individual reports on user interaction as well as security related actions as login/logout.

Fact Tables

• The following tables are used to collect facts from the JobScheduler history.
• These tables can be used to create individual reports.
• SQL Scripts: reporting.sql
• SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

PARENT_FOLDER

STATE

STATE_TEXT

IS_RUNTIME_DEFINED

RESULT_START_CAUSE

RESULT_STEPS

RESULT_ERROR

RESULT_ERROR_CODE

RESULT_ERROR_TEXT

TASK_ID

CLUSTER_MEMBER_ID

FOLDER

EXIT_CODE

AGENT_URL

SYNC_COMPLETED

RESULTS_COMPLETED

IS_ORDER

CLUSTER_MEMBER_ID

FOLDER

EXIT_CODE

AGENT_URL

SYNC_COMPLETED

RESULTS_COMPLETED

Aggregation Tables

• The following tables contain aggregations for 
• triggers, executions and tasks per day, week, month, quarter, year

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
  • SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.
     

Standard Procedures

Cleanup

• SQL Scripts: reporting_cleanup_procedure.sql
  • SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

Cleanup all entries

• cleanup all entries (reporting, dailyplan, yade)

• SELECT REPORT_CLEANUP('all',0);

  • following tables will be truncated: 

    • REPORTING_TRIGGERS
    • REPORTING_EXECUTIONS
    • REPORTING_TASKS
    • REPORTING_EXECUTION_DATES
    • DAILY_PLAN
    • JADE_FILES
    • JADE_FILES_HISTORY
    • JADE_FILES_POSITIONS 

• cleanup only reporting entries

  • SELECT REPORT_CLEANUP('reporting',0); 

    • following tables will be truncated:

      • REPORTING_TRIGGERS

      • REPORTING_EXECUTIONS

      • REPORTING_TASKS

      • REPORTING_EXECUTION_DATES 

• cleanup only dailyplan entries

  • SELECT REPORT_CLEANUP('dailyplan',0);
    • following tables will be truncated

      • DAILY_PLAN 

• cleanup only yade entries

  • SELECT REPORT_CLEANUP('yade',0);

    • following tables will be truncated:

      • JADE_FILES

      • JADE_FILES_HISTORY

      • JADE_FILES_POSITIONS 

Cleanup entries older as n days 

Example - cleanup entries older as 14 days 

• cleanup all entries (reporting, dailyplan, yade)
  • SELECT REPORT_CLEANUP('all',14);
• cleanup reporting entries
  • SELECT REPORT_CLEANUP('reporting',14);
• cleanup reporting entries
  • SELECT REPORT_CLEANUP('reporting',14);
• cleanup reporting entries
  • SELECT REPORT_CLEANUP('reporting',14)

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
  • SQL scripts in this article are provided for PostgreSQL. The SQL scrpts for the respective DBMS are provided by the JobScheduler installer.

Report about Installed Objects

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

• SELECT <fileds> FROM REPORT_INSTALLED_JOB_OBJECTS('2017-05-01','2017-06-01');
  • following fields can be selected:
    • ID
    • SCHEDULER_ID
    • HOSTNAME
    • JOB_CHAIN
    • JOB_CHAIN_BASENAME
    • JOB_CHAIN_TITLE
    • JOB_NAME
    • JOB_BASENAME
    • JOB_TITLE
    • CAUSE
    • IS_ACTIVE_CALENDAR
    • IS_ORDER_JOB
    • FILE_CREATED
    • FILE_MODIFIED 
• SELECT <fields> FROM REPORT_INSTALLED_ORDER_OBJECTS('2017-05-01','2014-06-01');
• following fields can be selected:
• ID
• SCHEDULER_ID
• HOSTNAME
• JOB_CHAIN
• JOB_CHAIN_BASENAME
• JOB_CHAIN_TITLE
• ORDER_ID
• ORDER_NAME
• ORDER_BASENAME
• ORDER_TITLE
• CAUSE
• IS_ACTIVE_CALENDAR
• FILE_CREATED
• FILE_MODIFIED