Get schedule from all jobs via SQL

73 views
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%');

 job_id |       job_name        |   schedule   |   time_zone   |  active  | subschedule | subschedule_name | from | to | subschedule_active | filter | filter_type |   calendar   | calendar_select_on | select_mode |   selection   
--------+-----------------------+--------------+---------------+----------+-------------+------------------+------+----+--------------------+--------+-------------+--------------+--------------------+-------------+---------------
  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') );

 interval_id |     interval_name     | dispatcher | dispatcher_name | dispatcher_enabled | from | to | select_interval | dispatcher_active | filter | filter_type |       calendar        | calendar_select_on | select_mode | selection  
-------------+-----------------------+------------+-----------------+--------------------+------+----+-----------------+-------------------+--------+-------------+-----------------------+--------------------+-------------+------------
      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 | FIXED_COMMON_WORKDAYS |          1 | DEFAULT         | ENABLED            |      |    |                 | ACTIVE            |      1 | DOW         |                       |                    | INVERSE     | Saturday
      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      | 
      143185 | WORKDAYS_ANY          |          1 | BAVARIA         | ENABLED            |      |    | WD_BAVARIA      | ACTIVE            |        |             |                       |                    | NORMAL      | 
      143185 |                       |          2 | BERLIN          | ENABLED            |      |    | WD_BERLIN       | ACTIVE            |        |             |                       |                    | NORMAL      | 
      143185 |                       |          3 | HAMBURG         | ENABLED            |      |    | WD_HAMBURG      | ACTIVE            |        |             |                       |                    | NORMAL      | 
      143185 |                       |          4 | DEFAULT         | ENABLED            |      |    |                 | INACTIVE          |        |             |                       |                    | 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:
Schedules.png

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