• 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 10

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

• Previous Change: Difference between versions 9 and 10
• View Page History

Key

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

Info
title In process

Table of Contents

Scope

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

  
  

  Expand
  title INVENTORY_AGENT_CLUSTERMEMBERS
  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
  AGENT_CLUSTER_ID	NOT NULL	NUMERIC	Foreign Key	X	references INVENTORY_AGENT_CLUSTERS.ID
  AGENT_INSTANCE_ID	NOT NULL	NUMERIC	Foreign Key	X	references INVENTORY_AGENT_INSTANCES.ID
  URL	NOT NULL	VARCHAR

    

  			agent url references INVENTORY_AGENT_INSTANCES.URL
  ORDERING	NOT NULL	NUMERIC

     

  		Order of agents in remote_schedulers element
  CREATED	NOT NULL	DATETIME

    UTC

  		UTC timestamp of record creation
  MODIFIED	NOT NULL	DATETIME

    

  UTC timestamp of record modification

Expand
title INVENTORY_AGENT_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
PROCESS_CLASS_ID	NOT NULL	NUMERIC	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_FILES
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
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

 

		X	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

		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

  

			UTC timestamp of record creation
MODIFIED	NOT NULL	DATETIME

  

UTC timestamp of record modification

  

 

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

OS_ID	NOT NULL	NUMERIC	Foreign Key

 

		references INVENTORY_OPERATING_SYSTEMS.ID
SCHEDULER_ID	NOT NULL	VARCHAR

 

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

 

		X	host on which the JobScheduler instance has been installed
PORT	NOT NULL	NUMERIC

 

X

TCP

HTTP 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
VERSION	NOT NULL	VARCHAR

   

		JobScheduler version e.g. 1.11.0
COMMAND_URL	NOT NULL	VARCHAR

   

		HTTP URL for XML request response
URL	NOT NULL	VARCHAR

   

		TCP URL still used by supervisor
TIMEZONE	NOT NULL	VARCHAR

   

CLUSTER_TYPE	NOT NULL	VARCHAR

  

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

   

DBMS_VERSION

 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

   

AUTH

 VARCHAR  

		reference INVENTORY_INSTANCES.ID
AUTH		VARCHAR

  

CREATED	NOT NULL	DATETIME

  

			UTC timestamp of record creation
MODIFIED	NOT NULL	DATETIME

  

UTC timestamp of record modification

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

 

DELAY 

 NUMERIC    

DIRECTORY 

 VARCHAR     

REGEX 

 VARCHAR    

ON_ERROR

 VARCHAR    

	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    

	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 timestamp of record creation
MODIFIED	NOT NULL	DATETIME

  

UTC timestamp of record modification

PROCESS_CLASS_ID

FW_PROCESS_CLASS_ID

MAX_ORDERS

DISTRIBUTED

PROCESS_CLASS

PROCESS_CLASS_NAME

FW_PROCESS_CLASS

FW_PROCESS_CLASS_NAME

PROCESS_CLASS_ID

SCHEDULE_ID

USED_IN_JOB_CHAINS

PROCESS_CLASS

PROCESS_CLASS_NAME

SCHEDULE

SCHEDULE_NAME

FILE_ID

NAME

order name

BASENAME

MAX_NON_EXCLUSIVE

specifies if a start time has been configured:

NAME

ARCHITECTURE

DISTRIBUTION

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


corresponds to SCHEDULER_ORDER_HISTORY.HISTORY_ID


corresponds to SCHEDULER_ORDER_HISTORY.ORDER_ID

order title

corresponds to INVENTORY_JOB_CHAIN_ORDERS.TITLE

JOB_CHAIN_ID

SCHEDULE_ID

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:

• 1: yes
• 0: no

SCHEDULE

SCHEDULE_NAME

INITIAL_STATE

END_STATE

PRIORITY

FILE_ID

NAME

BASENAME

MAX_PROCESSES

HAS_AGENTS

corresponds to SCHEDULER_ORDER_STEP_HISTORY.STEP

corresponds to SCHEDULER_HISTORY.JOB_NAME

e.g. test/my_job

job base name

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

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

specifies if a start time has been configured:

• 1: yes
• 0: no

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

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.

references REPORT_TRIGGERS.ID

references SCHEDULER_HISTORY.CAUSE of the first step

specifies the number of job steps that have been executed

specifies if an error occurred

• 0: no error
• 1: error

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.

references REPORT_TRIGGERS.ID or REPORT_EXECUTIONS.ID

type of reference:

• 0: references REPORT_TRIGGERS
• 1: references REPORT_EXECUTIONS

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

e.g. 2015

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

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.

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
)

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