Creating Schedules

Creating a Schedule

You can create a new schedule using this procedure:

SQL

SYSADMIN.CreateSchedule(<IN biginteger jobId>, <IN string type>, <IN biginteger intervl>, <IN biginteger startDelay>, <IN string cronExpression>, <IN boolean enabled>, <IN biginteger chainedToScheduleId>, <IN string chainCondition>, <IN string chainString>, <IN string scheduleName>, <IN string queueHandler>)

It takes the following parameters:

To view the full table, click the expand button in its top right corner

Parameter	Description
`jobId`	Id of the job the schedule belongs to (id of a ScheduleJob).
`type`	Can take one of four possible values; depending on the value, some other parameters are required or disregarded: `once`: only `startDelay` and the enabled parameters are used `intervl`: `interval` and `startDelay` parameters are used, `cronExpression`, `chainedToScheduleId`, and `chainCondition` are disregarded `cron`: `cronExpression` and `startDelay` parameters are used, `intervl`, `chainedToScheduleId`, and `chainCondition` are disregarded `chained`: only `chainedToScheduleId` and `chainCondition` parameters are used
`intervl`	How often to repeat the job in minutes. If this parameter is used, the `cronExpression` parameter is disregarded
`startDelay`	Initial delay before running the job (only before the 1st iteration, all subsequent iterations are fired in accordance with interval/cron schedule settings)
`cronExpression`	Provides the Quartz Cron Expression to specify the schedule. If this parameter is used, the `intervl` parameter is disregarded, but the `startDelay` parameter is used. This means that depending on the actual configuration, the event may or may not fire at this specific time; however, the trigger will not fire before this time. If we start a schedule at 17:59 with cron string having '18:00' and set the start delay to 2 minutes, then the event will fire at 18:00 of the following day
`enabled`	Enables the execution of the schedule according to `interval` or `cronExpression` parameters
`chainedToScheduleId`	ID of the parent schedule, i.e. this schedule will be executed after the parent schedule if `chainCondition` is fulfilled
`chainCondition`	Can take the following possible values: `UNCONDITIONAL`: this schedule is always executed after the parent schedule) `SUCCESS`: this schedule is only executed if parent schedule succeeds `FAILURE`: this Schedule is only executed if parent Schedule fails If `NULL` is provided, `UNCONDITIONAL` is set as default
`chainString`	Expression string when complex dependencies are used; see the section further down for a detailed explanation. If the created schedule depends only on one other schedule, either `chainedToScheduleId` is not `NULL` and `chainString` is `NULL`, or `chainedToScheduleId` is `NULL`, `chainCondition` is `NULL`, and `chainString` is not `NULL`
`scheduleName`	Meant to support synchronization processes between the Data Virtuality Server instances. It is not meant to be freely editable or selectable and should not be modified without very good reason. When `scheduleName` is absent from the procedure call, it is created by the system automatically. The system will also create `scheduleName` on the Data Virtuality Server start when it encounters any schedule without `scheduleName` set
`queueHandler`	Name of queue handler. If not set, `system` will be used for system jobs, and `default` will be used for other jobs

Examples

1. Creating a simple schedule that is executed only once:

SQL

CALL SYSADMIN.CreateSchedule(135, 'once', 0, 0, NULL, true, null, null, null)

2. Creating and immediately running a simple schedule with a given interval and startDelay:

SQL

CALL SYSADMIN.CreateSchedule(1, 'interval', 1, 0, null, true, null, null, null)

3. Creating a schedule with a cron expression. For example, the first one will start every Monday and Tuesday at 3:00 am; the second one starts every day at 5:00 am:

SQL

CALL SYSADMIN.CreateSchedule(321, 'cron', 1, 1, '0 0 3 ? * MON,TUE *', true, null, null, null)
CALL SYSADMIN.CreateSchedule(159, 'cron', 1, 1, '0 0 5 1/1 * ? *', true, null, null, null)

4. Creating a chained schedule for the job with ID 3 that is executed after the schedule with ID 15 has finished successfully:

SQL

CALL SYSADMIN.CreateSchedule(3, 'chained', null, 0, null, true, 15, 'SUCCESS', null)

Cron Expressions

Cron expressions use the Quartz Scheduler's syntax. To easily build an expression for a specific schedule, please check the CronMaker site.

The following sections originate from the CronTrigger tutorial and org.quartz.CronExpression documentation and are copyrighted under Apache License 2.0.

Syntax

These expressions are used to configure instances of CronTrigger. They are strings that are actually made up of seven sub-expressions describing individual details of the schedule. These sub-expressions are separated with whitespaces and represent the following elements:

Seconds
Minutes
Hours
Day of month
Month
Day of week
Year (optional field)

An example of a complete cron-expression is the string 0 0 12 ? * WED which means "every Wednesday at 12:00:00 pm".

Individual sub-expressions can contain ranges and/or lists. For example, the day of week field in the previous example (which reads WED) could be replaced with MON-FRI, MON,WED,FRI, or even MON-WED,SAT.

All fields have a set of valid values that can be specified. These values should be fairly obvious - such as the numbers from 0 to 59 for seconds and minutes, and the values from 0 to 23 for hours. Day of the month can be any value from 1 to 31, but you must be careful about how many days are in a given month! Months can be specified as values between 0 and 11, or by using the strings JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, and DEC. Days of the week can be specified as values between 1 and 7 (1 = Sunday) or by using the strings SUN, MON, TUE, WED, THU, FRI, and SAT.

Field name	Allowed values	Allowed special characters
`Seconds`	`0-59`	`, - * /`
`Minutes`	`0-59`	`, - * /`
`Hours`	`0-23`	`, - * /`
`Day of month`	`1-31`	`, - * ? / L W`
`Month`	`1-12 or JAN-DEC`	`, - * /`
`Day of Week`	`1-7 or SUN-SAT`	`, - * ? / L #`
`Year (optional)`	`empty, 1970-2199`	`, - * /`

To view the full table, click the expand button in its top right corner

Character	Possible field(s)	Meaning
-	All	Ranges: `1-4` or `SUN-WED`
,	All	Additional values: `MON,WED,FRI`
`*`	All	Every possible value of this field. Thus, the `*` in the Month field of the previous example simply means "every month"
`/`	All	Increments to values. For example, `0/15` in the Minutes field means 'every 15th minute of the hour, starting at minute zero'. If you use `3/20` in the Minutes field, it would mean 'every 20th minute of the hour, starting at minute three' - or in other words, it is the same as specifying `3,23,43` in the Minutes field. Please note that `/35` does not mean "every 35 minutes" - it means "every 35th minute of the hour, starting at minute zero" - or, in other words, the same as specifying `0,35`.
`?`	Day of month Day of week	No specific value. This is useful when you need to specify something in one of the two fields, but not the other. See the examples below (and CronTrigger JavaDoc) for clarification
`L`	Day of month Day of week	Short-hand for "last", but has a different meaning in each of the two fields: Day of month: "the last day of the month" - day 31 for January, day 28 for February on non-leap years Day of week: if used by itself, simply means `7` or `SAT`. But if used in the day of week field after another value, it means "the last xxx day of the month" - for example, `6L` or `FRIL` both mean "the last Friday of the month". You can also specify an offset from the last day of the month, such as `L-3`, which would mean the third-to-last day of the calendar month. Please note that when using the `L` option, it is important not to specify lists, or ranges of values, as you may get confusing/unexpected results
`W`	Day of month Day of week	Weekday (Monday to Friday) closest to the given day. For example, `15W` means "the nearest weekday to the 15th of the month"
#	Day of week	The Nth XXX weekday of the month. For example, `6#3` or `FRI#3` means "the third Friday of the month"

Here are a few more examples of expressions and their meanings - you can find even more in the JavaDoc for org.quartz.CronExpression.

Examples

1. Creating a trigger that fires every 5 minutes:

CODE

0 0/5 * * * ?

2. Creating a trigger that fires every 5 minutes, at 10 seconds after the minute (i.e. 10:00:10 am, 10:05:10 am, etc.):

CODE

10 0/5 * * * ?

3. Creating a trigger that fires at 10:30, 11:30, 12:30, and 13:30, every Wednesday and Friday:

CODE

0 30 10-13 ? * WED,FRI

4. Creating a trigger that fires every half hour between 8 am and 10 am on the 5th and 20th of every month. Note that the trigger will NOT fire at 10:00 am, just at 8:00, 8:30, 9:00 and 9:30:

CODE

0 0/30 8-9 5,20 * ?

Please note that some scheduling requirements are too complicated to express with a single trigger - such as "every 5 minutes between 9:00 am and 10:00 am, and every 20 minutes between 1:00 pm and 10:00 pm". The solution in this scenario is to simply create two triggers, and register both to run the same job.

Schedules Table

To view the full table, click the expand button in its top right corner

Value	Description
`id`	Unique id of the schedule itself
`jobID`	Id of the job the schedule belongs to (id of a ScheduleJob)
`type`	Can take one of four possible values; depending on the value, some other parameters are required or disregarded: `once`: only `startDelay` and the enabled parameters are used `interval`: `intervl` and `startDelay` parameters are used, `cronExpression` has `NULL` value `cron`: `cronExpression` and `startDelay` parameters are used, `intervl` has no meaningful value `chained`: `chainedTo` and `chainCondition` parameters are used
`intervl`	How often to repeat the job in minutes
`startDelay`	Initial delay before running the job (only before the 1st iteration, all subsequent fire in accordance with interval/cron schedule settings)
`cronExpression`	Quartz Cron Expression which specifies the schedule
`enabled`	Whether the schedule is active or not
`chainString`	Logical expression for the dependencies on other jobs
`nextFireTime`	Date and time of the subsequent planned job fire (if the schedule is active, i.e. the enabled field contains `TRUE`)