Get schedule from all jobs via SQL

64 views

general-discussions

Skip to first unread message

Fábio Carvalho

unread,

Feb 10, 2020, 11:50:49 AM2/10/20

to schedulix

Hi,

I have to generate a massive documentation about my proceses in schedulix, i need the name, program to run and time.

Name and Program its ok for me to get.

How can i get the schedules and its properties via SQL?

For batches how can i get it´s children?

Thanks, any help is useful.

Fábio

Dieter Stubler

unread,

Feb 14, 2020, 6:22:19 AM2/14/20

to schedulix

Hi Fabio,

since the internal time scheduling engine is quite abstract but extremely powerful, it is nearly impossible to get a good readable time scheduling reporting using SQL for any possible schedule.
If the schedules and intervals are created using the web gui, those schedules follow rules which can be used to implement a time scheduling sql reporting.

We have done that for postgresql.

This is not a easy task to implement for all supported rdbms system.

Maybe the community will come up with implementations for other rdbms systems.

As an example I attached a postgresql sql script to create the functions and views for sql reporting of time schedules.

To get good results you have to:

use schedulix 2.9+
use a postgresql repository
schedules have to be created using the web gui and have been stored with version set to 2.9

Here is some example output:

SELECT *

FROM xci_schedules_report

WHERE job_id IN (SELECT id from sci_c_scheduling_entity WHERE name LIKE 'Beispiel%');

--------+-----------------------+--------------+---------------+----------+-------------+------------------+------+----+--------------------+--------+-------------+--------------+--------------------+-------------+---------------

95464 | SYSTEM.Beispiel Job | MONDAY_10 | Europe/Berlin | INACTIVE | 1 | DEFAULT | | | ACTIVE | 1 | TOD | | | NORMAL | 00:00

95464 | | | | | | | | | | | | | | | 01:00

95464 | | | | | | | | | | | | | | | 02:00

95464 | | | | | | | | | | | | | | | 03:00

95464 | | | | | | | | | | | | | | | 23:55

95464 | | | | | | | | | | 2 | CLF | ARBEITSTAGE | DAY | NORMAL |

95464 | | | | | | | | | | 3 | WOM | | | NORMAL | 1

95464 | | | | | | | | | | | | | | | -1

95464 | | | | | | | | | | 4 | DOW | | | NORMAL | Monday

95464 | | | | | | | | | | | | | | | Thursday

95464 | | | | | | | | | | 5 | ROD | | | NORMAL | 02:00 - 16:00

95464 | | WORKDAY_ALL | Europe/Berlin | INACTIVE | 1 | DEFAULT | | | ACTIVE | 1 | TOD | | | NORMAL | 10:00

95464 | | | | | | | | | | 2 | CLF | WORKDAYS_ALL | DAY | NORMAL |

95464 | | WORKDAY_ANY | Europe/Berlin | INACTIVE | 1 | DEFAULT | | | ACTIVE | 1 | TOD | | | NORMAL | 10:00

95464 | | | | | | | | | | 2 | CLF | WORKDAYS_ANY | DAY | NORMAL |

155237 | SYSTEM.Beispiel Job 2 | FRIDAY_NIGHT | Europe/Berlin | ACTIVE | 1 | DEFAULT | | | ACTIVE | 1 | TOD | | | NORMAL | 23:55

155237 | | | | | | | | | | 2 | DOW | | | NORMAL | Friday

(17 rows)

SELECT *

FROM xci_calendars_report

WHERE interval_id IN ( SELECT ID FROM sci_intervall WHERE name in ('WORKDAYS_ANY', 'WORKDAYS_ALL', 'WD_BAVARIA','FIXED_COMMON_WORKDAYS', 'COMMON_WORKDAYS') );

-------------+-----------------------+------------+-----------------+--------------------+------+----+-----------------+-------------------+--------+-------------+-----------------------+--------------------+-------------+------------

143026 | COMMON_WORKDAYS | 1 | DEFAULT | ENABLED | | | | ACTIVE | 1 | LST | | | INVERSE | 2020.04.10

143026 | | | | | | | | | | | | | | 2020.06.01

143026 | | | | | | | | | | | | | | 2020.05.21

143026 | | | | | | | | | | | | | | 2020.04.13

143026 | | | | | | | | | 2 | CLF | FIXED_COMMON_WORKDAYS | DAY | NORMAL |

143013 | | | | | | | | | | | | | | Sunday

143013 | | | | | | | | | 2 | LST | | | INVERSE | -12-25

143013 | | | | | | | | | | | | | | -12-26

143013 | | | | | | | | | | | | | | -05-01

143013 | | | | | | | | | | | | | | -10-03

143013 | | | | | | | | | | | | | | -01-01

143036 | WD_BAVARIA | 1 | DEFAULT | ENABLED | | | | ACTIVE | 1 | LST | | | INVERSE | 2020.06.11

143036 | | | | | | | | | | | | | | 2020.08.15

143036 | | | | | | | | | | | | | | -01-06

143036 | | | | | | | | | | | | | | -11-01

143036 | | | | | | | | | 2 | CLF | COMMON_WORKDAYS | DAY | NORMAL |

143177 | WORKDAYS_ALL | 1 | DEFAULT | ENABLED | | | | ACTIVE | 1 | CLF | WD_BAVARIA | DAY | NORMAL |

143177 | | | | | | | | | 2 | CLF | WD_BERLIN | DAY | NORMAL |

143177 | | | | | | | | | 3 | CLF | WD_HAMBURG | DAY | NORMAL |

(24 rows)

To get the already known execution times for scheduled jobs and batches you can also enable the CALENDAR in you schedules an then query the sci_calendar view or use the 'FUTURE' select option in the monitoring master view of the web gui.

For parent child relationships of jobs and batches the sci_c_scheduling_hierarchy table is your friend.

Hope this helps you further.

Regards
Dieter

pgUtil.sql

domini...@conrad.de

unread,

Dec 7, 2022, 9:47:04 AM12/7/22

to schedulix

Dear schedulix team,

I have the following situation.

I have a web application, where our business department can configure product exports, which will be scheduled once per week, once per day or even once every 6 hours.

The web application sends this information to our schedulix-api, which will create the SDMS commands for creating the job in schedulix:

interval
schedule
event
scheduledEvent

The schedulix GUI overview for those entries looks like this:

How can I get now via SDMS command or maybe via SQL query all the schedules for one job?

Our goal is to visualize also the Schedules in our web application, which are configured in the schedulix environment.

Our problem is, that we have the schedules saved in our database and also in the schedulix database, but the schedules are not synched.
So we have currently the situation, that a job has a visible schedule in our web application, but not in the schedulix environment (or the other way around).

Thanks in advance.

Best regards

Dominik Bahl

Ronald Jeninga

unread,

Dec 7, 2022, 10:29:36 AM12/7/22

to schedulix

Hi Dominik,

clearly, the entries in the schedulix environment tell you what's going to happen, not the entries in your web application.

If I assume that the screen shot is just an example and all other schedules are set up similarly, the Calendar checkbox is checked for all of them.

This again means that you'll have the full list of planned executions (up to some time in the future) within the Calendar table.

So either you ask the server like:

list calendar with filter = (name like '.*SINGLEJOB');

ID SE_NAME SE_TYPE SE_ID SE_OWNER SE_PRIVS SCE_NAME SCE_ACTIVE EVT_NAME STARTTIME EXPECTED_FINAL_TIME TIME_ZONE
5007 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE2 true S1095_SCHEDULE2 2022-12-08T00:00:00 Europe/Berlin 0 Europe/Berlin
4910 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE1 true S1095_SCHEDULE1 2022-12-08T09:00:00 Europe/Berlin 0 Europe/Berlin
5197 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE3 true S1095_SCHEDULE3 2022-12-08T10:00:00 Europe/Berlin 0 Europe/Berlin
5008 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE2 true S1095_SCHEDULE2 2022-12-08T12:00:00 Europe/Berlin 0 Europe/Berlin
5198 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE3 true S1095_SCHEDULE3 2022-12-08T13:00:00 Europe/Berlin 0 Europe/Berlin
5199 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE3 true S1095_SCHEDULE3 2022-12-08T16:00:00 Europe/Berlin 0 Europe/Berlin
5009 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE2 true S1095_SCHEDULE2 2022-12-09T00:00:00 Europe/Berlin 0 Europe/Berlin
4911 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE1 true S1095_SCHEDULE1 2022-12-09T09:00:00 Europe/Berlin 0 Europe/Berlin
5200 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE3 true S1095_SCHEDULE3 2022-12-09T10:00:00 Europe/Berlin 0 Europe/Berlin
5010 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE2 true S1095_SCHEDULE2 2022-12-09T12:00:00 Europe/Berlin 0 Europe/Berlin
5201 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE3 true S1095_SCHEDULE3 2022-12-09T13:00:00 Europe/Berlin 0 Europe/Berlin
5202 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB JOB 1095 PUBLIC KPDEMOSVGR ROOT.S1095.SCHEDULE3 true S1095_SCHEDULE3 2022-12-09T16:00:00 Europe/Berlin 0 Europe/Berlin

...

(There was a lot more output. Look at syntax.pdf for more filter options)

Or you use SQL like:

select c.starttime, sce.name, evt.se_id
from sci_calendar c,
sci_scheduled_event scev,
sci_schedule sce,
sci_event evt
where c.scev_id = scev.id
and scev.sce_id = sce.id
and scev.evt_id = evt.id
order by 1
with se_id job
;

STARTTIME NAME SE_ID
2022-12-08 00:00:00.0 SCHEDULE2 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-08 09:00:00.0 SCHEDULE1 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-08 10:00:00.0 SCHEDULE3 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-08 12:00:00.0 SCHEDULE2 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-08 13:00:00.0 SCHEDULE3 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-08 16:00:00.0 SCHEDULE3 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-09 00:00:00.0 SCHEDULE2 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-09 09:00:00.0 SCHEDULE1 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-09 10:00:00.0 SCHEDULE3 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-09 12:00:00.0 SCHEDULE2 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-09 13:00:00.0 SCHEDULE3 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB
2022-12-09 16:00:00.0 SCHEDULE3 SYSTEM.EXAMPLES.E0010_SINGLEJOB.SINGLEJOB

...

(and again there was a lot more output; I assume you are familiar enough with SQL to filter out the rows you don't need).

Does this help?

Best regards,

Ronald

domini...@conrad.de

unread,

Dec 15, 2022, 5:41:50 AM12/15/22

to schedulix

Hi Ronald,

the select commands seems for me the right solution.

Currently I can select the next executions for every schedule.

But i don't know, how I can select the configuration of my schedule.

As you can see in my picture from above, I would like to receive in the interval section the Type and Setup.

In my picture it is Type = Repeat and Setup = T06:00.

But in the database I don't know which tables or views are the correct ones:
sci_intervall

sci_interval_selection

sci_interval_dispatcher

sci_interval_hierarchy

We are using Schedulix 2.9.

Do I need now the new views, which Dieter posted in the answer of this thread?

Thanks

Best regards,
Dominik

Ronald Jeninga

unread,

Dec 16, 2022, 3:38:32 AM12/16/22

to schedulix

Hi Dominik,

the bad news is that you'd need all tables to extract a complete Interval definition.

In fact, I think it isn't feasible to reverse engineer an Interval definition and translate it into plain text.

It might work in some simple cases though.

I had a short look at the new view definitions written by Dieter.

Their complexity more or less shows what I meant in the first paragraph above.

An Interval that does nothing more than a "repeat" or an "every Monday at 10:00" will be relatively easy to recognize.

But in the common case, it'll be hard.

Just as an example of a more common case, I've seen requirements like:

A Batch should be submitted every 28th of the month, unless it is not a business day, in which case the Batch has to be submitted at the previous business day.

If I translate it to an Interval definition, the first thing that catches ones eye is the term "business day".

Usually it is all Mondays til Fridays with the exception of public holidays.

Hence we need an Interval to define the public holidays (or better the negation) first. It looks like

CREATE OR ALTER INTERVAL NON_HOLIDAYS

WITH

BASE = 1 DAY,

DURATION = 1 DAY,

SELECTION = (

'-12-25', '-12-26', /* Christmas every year */

'-12-31', '-01-01', /* Sylvester and New Years Day */

'2023-06-25' /* The boss's birthday */

), INVERSE;

In order to select Monday til Friday, we use something like:

CREATE OR ALTER INTERVAL BUSINESS_DAYS

WITH

BASE = 1 DAY,

DURATION = 1 DAY,

SELECTION = (1, 2, 3, 4, 5),

FILTER = (NON_HOLIDAYS);

This is basically all Mo-Fr without the holidays.

Now we need the 28th, 27th, 26th, 25th, 24th and 23th.

If the 28th is a Sunday in December, the 27th is a Saturday and the days before is Christmas. Hence the Job should run at the 24th.

For simplicity reasons I assume there are no more than three holidays in a row.

That looks like

CREATE OR ALTER INTERVAL TWENTY_EIGHTH_OR_BD_BEFORE

WITH

BASE = 1 MONTH,

DURATION = 1 DAY,

SELECTION = (28, 27, 26, 25, 24, 23),

FILTER = (BUSINESS_DAYS);

Finally we can complete the task:

CREATE OR ALTER INTERVAL FINAL_SCHEDULE

WITH

EMBEDDED = TWENTY_EIGHTH_OR_BD_BEFORE,

SELECTION = (-1); /* this selects the last day that isn't filtered out; usually the 28th */

This Interval can now be used as a filter in the GUI. (See screen shot).

If you now look at the structure of the statements, you see several selections. These are stored in the interval_selection table.

You also see a number of filters (non_holidays is used to filter the business_days, which is used to filter the twentyeight_or_bd_before), which are stored in the interval_hierarchy table.

I didn't use a dispatcher because I didn't need it here. But there are more possible implementations of the example requirement and it would have been possible to use a dispatcher, which is stored in the interval_dispatcher table.

Besides that, the GUI makes heavy use of the dispatcher Intervals. Hence you'll have to consider the table too.

The basic objects are stored in the intervall table (note the double "l", added to prevent name clashes with reserved words).

I think the easiest way to obtain the information is to add some hints in the name of the schedule.

Instead of "DAILY" you call it "DAILY_REP0600" or "DAILY_AT1000".

If this is not an option, I think the best alternative is to use Dieter's views and find out how to recognize the idea behind the schedules.

Small note: if you want to try the above, the 28th isn't a weekend in the next few months. You could adapt the scheme and use the 26th instead to see it working.

Best regards,

Ronald

dominik.png

Reply all

Reply to author

Forward

0 new messages