Chained Schedules

Chained schedules come in handy when you need a replication job to run after one or more other job has run: for example, if you have two views to be replicated and one view uses the other. The Data Virtuality Server can set and use dependencies between schedules of replication jobs. In this section, we describe the basic syntax for chained schedules and the two types of dependencies: simple (when the schedule is linked to just one other schedule or job) and complex (with more than one other schedule or job involved).

Creating a Chained Schedule

Chained schedules are created via the createSchedule stored procedure:

SQL

CALL SYSADMIN.createSchedule"(
	"jobId" => <biginteger jobId>,
	"type" => 'chained',
	"intervl" => 0 ,
	"startDelay" => 0,
	"cronExpression" => null,
	"enabled" => <bool enabled>,
	"chainedToScheduleId" => <biginteger chainedToScheduleId>,
	"chainCondition" => <string chainCondition>,
	"chainString" => <string chainString>,
	"scheduleName" => NULL,
	"jobUuid" => <string jobUuid>
);;

jobId or jobUuid should be specified.

This procedure takes the following parameters common for schedules with both simple and complex dependencies:

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

Parameter	Description
`jobId`	Replication job id for which this schedule will be created
`type`	Type of schedule; here, it is always `chained`
`intervl`	Always `0` for this type of schedule
`startDelay`	Always `0` for this type of schedule
`cronExpression`	Always `NULL` for this type of schedule
`enabled`	Whether the schedules will actively be in use. Possible values are `TRUE` or `FALSE`
`scheduleName`	Name of the schedule; may be provided on schedule creation. If not provided, will be generated by the system
`jobUuid`	Replication job UUID for which this schedule will be created

The following parameters are specific for chained schedules with a simple dependency:

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

jobUuid and uuid parameters in SYSADMIN.createSchedule are available since v4.1

Parameter Description

chainedToScheduleId

Identifies the schedule the new schedule will be chained to for simple dependencies. Set to NULL in case of a complex dependency.

chainCondition

Status of the chained schedule's run in case of simple dependency. Set to NULL in case of a complex dependency.

The value specifies which status is required to trigger the schedule. Possible values are as follows:

NULL or UNCONDITIONAL: the outcome of the other schedule can be success or failure. The chained schedule will always be triggered;
SUCCESS: the chained schedule will be triggered only if the previous schedule has run successfully;
FAILURE: the chained schedule will be triggered only if the previous schedule has failed.

Chained job schedules ignore the interim FAILED_AUTORETRY job status and will be triggered only when the job schedule gets the final SUCCESS or FAILURE status

There is also one parameter which is specific for chained schedules with a complex dependency, but can also be used for defining a simple dependency:

Parameter Description

chainString

Defines the mode for a complex dependency (normal or atomic). Normal mode is defined by omitting any keyword for mode, while the atomic mode is defined by providing the atomic keyword for mode;
Identifies the schedules or jobs the new schedule will be chained to for complex dependencies. In case of a schedule, set id or uuid of of the schedule, and for a job, the id or uuid of the job with character j preceding the id. For example, chainString => '15' means chain to schedule with Id 15, while chainString => 'J15=SUCCESS' means chain to (any schedule of) the job with Id 15 which was run successfully;

Regardless of whether id or uuid are used to create a chained schedule in the config database, chainstring will be stored with uuid. For example chainString => 'J15=SUCCESS' will be stored as chainString => 'Jbd3c7690-677f-11ee-8c99-0242ac120002=SUCCESS' 
where  bd3c7690-677f-11ee-8c99-0242ac120002 is the uuid of the job with id 15;

Defines the chain condition for complex dependencies. For defining the conditions, the same values and rules apply as described for the simple dependency parameter chainCondition.

Please note that chainedToScheduleId/chainCondition and chainString are mutually exclusive. Either chainedToScheduleId/chainCondition or chainString can be used. The variant which is not used must be set to NULL in the createSchedule call.

Simple Dependency

A simple dependency means that a schedule is chained to exactly one other schedule or job. It can be defined in one of the following ways:

Using the chainedToScheduleId parameter to identify the schedule is chained to and the chainCondition to define the condition;
Using the chainString parameter.

Here are several examples:

1. Creating a simple dependency using the chainedToScheduleId and chainCondition parameters:

SQL

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, 10, NULL, NULL, NULL)

CALL SYSADMIN.createSchedule('chained', 0, 0, NULL, TRUE, 10, 'UNCONDITIONAL', NULL, NULL, 'bd3c7690-677f-11ee-8c99-0242ac120002')

Both statements create a new schedule for replication job 32779. The new schedule depends on the outcome of schedule 10. Since NULL and UNCONDITIONAL are semantically equal for this procedure, both scripts will always trigger the new schedule as soon as schedule number 10 runs.

The following two examples will trigger the new schedule only if the replication job has resulted in a specific outcome (success or failure, respectively):

SQL

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, 10, 'SUCCESS', NULL, NULL)

CALL SYSADMIN.createSchedule('chained', 0, 0, NULL, TRUE, 10, 'FAILURE', NULL, NULL, 'bd3c7690-677f-11ee-8c99-0242ac120002')

2. Creating a simple dependency using the chainString parameter:

SQL

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, NULL, NULL, 'J10', NULL)

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, NULL, NULL, 'Jbd3c7690-677f-11ee-8c99-0242ac120002=UNCONDITIONAL', NULL)

These example calls work just like the first two examples using chainedToScheduleId and chainCondition. The only difference is that the job id and its desired outcome are combined within chainString.

As for the case when the schedule should be triggered only if the replication job has resulted in a specific outcome, there are two ways to express it:

SQL

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, NULL, NULL, 'J10=SUCCESS', NULL)

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, NULL, NULL, '!(Jbd3c7690-677f-11ee-8c99-0242ac120002=FAILURE)', NULL)

!(10=FAILURE) is semantically equal to 10=SUCCESS. Please note that the statement to be negated should be enclosed in parentheses.

Complex Dependency

A complex dependency means that a schedule is chained to more than one other schedule or job. This is how it works:

The chained schedule waits for results from all base schedules.
The chained schedule fires when all base schedule job runs end with the appropriate result (i.e. the result specified in chainString).

How a complex dependency works in detail depends on the selected mode: normal or atomic.

Normal Mode (Default)

This is how a complex dependency works in the normal mode:

It is possible that a base schedule job has run multiple times within a cycle. All execution states reached within one cycle will be considered to check whether conditions for the chained schedule are met. So if a condition is that a particular base schedule job run finishes with success, and the base schedule did finish once with success and once with failure (in that order) within the cycle, the success condition will be considered as being met for a chained schedule in normal mode.
A new cycle starts only when the chained schedule has fired. The new cycle starts after the job assigned to the chained schedule has finished.
The cycle can be restarted manually using the resetAtomicSchedule stored procedure.

Atomic Mode

This is how a complex dependency works in the atomic mode:

It is possible that a base schedule job has run multiple times within a cycle. Only the state of the last execution will be considered to check whether conditions for the chained schedule are met. So if a condition is that a particular base schedule job run finishes with success, and the base schedule did finish once with success and once with failure (in that order) within the cycle, the success condition will not be considered as being met for a chained schedule in atomic mode.
A new cycle starts when all base schedules have fired, regardless of the results of their runs. So, no matter whether the chained schedule has fired, the cycle will be restarted after all base schedules have run at least once.
An atomic chained schedule that has not been fired on cycle restart because the conditions have not been met will have the NOT_STARTED state.
The cycle can be restarted manually using the resetAtomicSchedule stored procedure.

To enable the atomic mode, add the atomic keyword at the beginning of chainString.

Examples

Here is an example to show the difference between normal and atomic modes:

Normal mode:

SQL

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, NULL, NULL, '10=SUCCESS & 11=SUCCESS', NULL)

Atomic mode:

SQL

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, NULL, NULL, 'atomic 10=SUCCESS & 11=SUCCESS', NULL)

Here is what is happening:

Scheduled	Result
10	`SUCCESS`
11	`FAILURE`
A new cycle starts for the atomic chained schedule; the schedule is not triggered
11	`SUCCESS`
A normal chained schedule is triggered, and a new cycle starts
10	`SUCCESS`
An atomic chained schedule is triggered, and a new cycle starts
10	`FAILURE`
11	`SUCCESS`
A normal chained schedule is triggered, and a new cycle starts A new cycle starts for the atomic chained schedule; the schedule is not triggered
11	`SUCCESS`
10	`SUCCESS`
Both chained schedules are triggered, and new cycles start

More Examples

1. Two schedules are taken into consideration from the Server to determine whether the new schedule shall run. Job 10 and schedule 11 have both run successfully. Since job 10 and schedule 11 do not necessarily have to run simultaneously, the server will check their prior status as soon as one of the schedules finishes. Assuming that the last execution of job 10 was successful and schedule 11 runs now for the first time, as soon as schedule 11 finishes, the system will check both schedules' last status. This means that if schedule 11 finishes with failure, the new schedule will stay in sleep mode and start otherwise:

SQL

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, NULL, NULL, 'J10=SUCCESS & 11=SUCCESS', NULL)

Please note that this behaviour influences the running time of chained schedules.

2. Again, we have two schedules, 10 and 11, which are always successful. Additionally, schedule 10 always starts at xx:00 and xx:30 while Schedule 11 always starts at xx:15 and xx:45. Now, the following schedule is created:

SQL

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, NULL, NULL, '10=SUCCESS & 11=SUCCESS', NULL)

Whenever one of the two schedules finishes, the newly created schedule will be triggered since we assume them always to succeed. Hence, the new schedule will run effectively four times per hour (xx:00, xx:15, xx:30, and xx:45). This should be taken into consideration when a very complex chainString is used, and the schedules depend on more than two others.

3. These two examples show how to combine the different logical operators and chain conditions into one chainString:

SQL

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, NULL, NULL, '!(10=SUCCESS & 11=FAILURE)', NULL)

CALL SYSADMIN.createSchedule(32779, 'chained', 0, 0, NULL, TRUE, NULL, NULL, '10=FAILURE | 11=SUCCESS)', NULL)

They are identical and will be triggered when schedule 10 fails, or schedule 11 succeeds.

4. In this example, the schedule is triggered as soon as one of schedule 10, 11, or 22 fails:

SQL

CALL SYSADMIN.createSchedule(32781, 'chained', 0, 0, NULL, TRUE, NULL, NULL, '!(10=SUCCESS & 11=SUCCESS) | 22=FAILURE', NULL)