Scope

• Provide technical information on possible customizations of the data model.
• 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.

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

Application Joins to Jobs

Assigning Mandators to Jobs

Mandator Master Data

Mandator Joins to Jobs

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

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

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

Sample ODC Files

• The following ODC files show the typical way how to use a custom procedure and to insert the data into an Excel file.
• JobScheduler Reporting Interface - Custom Execution Summary.odc
• JobScheduler Reporting Interface - Custom Installed Objects.odc

Sample Excel Template

• The following template makes use of the above data model customizations.
• Document: JobSchedulerReporting Customization.xlsx

...