Scheduled Runs
Batches are used to execute multiple folders, worksheets (sheets), and transformations together, either on a schedule or on demand.
- Batches are organized in batch folders in the repository
- Batches are not deployed with sheets
Elements
A batch contains a list of Batch Elements. Each row is a start object. When the batch runs, folders, sheets, and nested batches are expanded to transformations (actions). Only those transformations are placed in the execution queue.
| Type | Meaning | What is started |
|---|---|---|
| Folder | Directory in the transformation tree | All sheets in that folder, recursively, and their transformations |
| Sheet | Worksheet | All transformations on that sheet |
| Action | Single transformation | That transformation |
| Batch | Another batch | The elements of the referenced batch |
You add elements with Add… or by dragging folders, sheets, and transformations from the tree. The same transformation is started only once if it appears through several paths.
The table columns Prio and Start Mode apply to the element and are inherited by the transformations it expands to.
Prio (priority): a smaller number is started first among waiting transformations. An empty value is treated as the lowest priority. Execution order also depends on dependencies drawn on the sheet and on how many jobs the server may run in parallel. The row order in the table is not the only order of execution.
Start Mode:
| Mode | Meaning |
|---|---|
| Normal | The transformation waits for its dependencies, then runs |
| Skipped | The transformation is not executed. It is marked finished immediately so dependent transformations can continue |
| Halted | The transformation is set to halted immediately and is not executed |
A batch with no elements cannot be started.
Scheduling
Scheduling is frequency-based (not a cron expression). The scheduler checks due batches about every 30 seconds. Dates are calculated in the server JVM time zone.
| Field | Meaning |
|---|---|
| Active | When selected, the batch is included in automatic scheduling. When cleared, only a manual Run starts it |
| Time | Clock time of the start as HH:MM |
| Frequency | How often the batch is planned |
| Day / Minutes | Extra offset; the label changes with the frequency |
| Frequency | Day / Minutes | Time |
|---|---|---|
| Daily | Not used | Start time each day |
| Weekly | Weekday offset 0–6 (0 = Sunday … 6 = Saturday) | Start time on that weekday |
| Monthly | Day of month: 0 = last day, 1 = first day … 31 = 31st | Start time on that day |
| Interval in Minutes | Repeat interval in minutes | Offset inside that interval |
The next automatic start is shown as Next Run (batch toolbar) or Next Start (batch overview). On Demand in the overview is a batch without a repeating schedule; it is started only with Run.
Manual Run (batch header, context menu, or overview) starts the batch immediately, independent of Active and of the next planned time. Runs describes how a run is created.
An interval of 0 minutes is invalid; automatic scheduling is then turned off.
Notifications
When a time-triggered batch is executed, the user can be informed by email about the execution status.
Recipients come from the batch mailing list for the chosen event. The joined address string uses the same TO: / CC: / BCC: syntax as other datasqill mail; see E-Mail.
The following events regarding the run status can be set:
| Name | Meaning |
|---|---|
| On Start | Message is sent when the run starts |
| On Retry | Message is sent when the scheduler automatically restarts |
| On Error | Message is sent when the run enters an error state |
| On Skip | Message is sent when an action is skipped |
| On Hold | Message is sent when the run is halted |
| On Success | Message is sent when the run has been completed successfully |
| On Action Error | Currently not implemented |
The format of the sent messages is specified via a Freemarker template and can be customized by the customer.
There is one template for the subject and one for the message content. The templates are stored in the table vw_sqts_conig_global:
| Type | config_key1 | config_key2 | config_key3 | config_key4 |
|---|---|---|---|---|
| Subject | SCHEDULER | TEMPLATE | Mail_Header | |
| Content | SCHEDULER | TEMPLATE | Mail_Content |
The following variables are made available in Freemarker:
| Variable | Data Type | Meaning |
|---|---|---|
| batchName | String | The batch name |
| eventType | String | See events for run status |
| instance | String | The environment name. Determined from vv_sqts_config_global ("SCHEDULER", "TRANSFORMATION", "INSTANCE_NAME") |
| planned | Timestamp | Planned time of the batch |
| when | String | Effective start time of the batch |
| resultsFromData | List | List of objects from the batch |
The list of objects from the batch contains all entries from Actions that were not successful on the first attempt. This list is sorted ascending by action ID and descending by start time.
Each object has a list of names and their values. The following names are available:
| Name | Data Type | Meaning |
|---|---|---|
| sqtsActionId | Number | The action ID |
| sqtsQueueId | Number | The associated queue entry ID |
| sqtsDiagramGroupName | String | The sheet name |
| actionName | String | The action name |
| status | Char | The status code of the entry |
| executionMode | Char | N=Normal, K = Skipped |
| insertUser | String | The name of the user who created the queue entry (initial datasqill) |
| startDt | Timestamp | Action start time |
| endDt | Timestamp | Action end time |
| errorCode | String | Error number or 0 if no error occurred |
| statusText | String | Error text |
A subject could look like this:
[#if eventType == 'ON START']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: started
[#elseif eventType == 'ON ACTION ERROR']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: ignored
[#elseif eventType == 'ON SKIP']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: skipped
[#elseif eventType == 'ON SUCCESS']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: finished
[#elseif eventType == 'ON ERROR']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: ERROR
[#elseif eventType == 'ON HOLD']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: Halted
[#elseif eventType == 'ON RETRY']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: Restarted
A simple email content could look like this:
[#assign statusCodes = { "E": "Error", "F": "Finished", "I": "Interupted", "H": "Halted" }]
[#if eventType == 'ON START']
Batch started, Planned start date: ${planned?string("dd.MM.yyyy HH:mm:ss")}. Effective start date: ${when?string("dd.MM.yyyy HH:mm:ss")}.
[#elseif eventType == 'ON SKIP']
Batch skipped at ${when?string("dd.MM.yyyy HH:mm:ss")}.
[#elseif eventType == 'ON SUCCESS']
Batch successfully completed at ${when?string("dd.MM.yyyy HH:mm:ss")}.
[#elseif eventType == 'ON ERROR']
Batch terminated with ERROR at ${when?string("dd.MM.yyyy HH:mm:ss")}.
[#elseif eventType == 'ON HOLD']
Batch went in halted state at ${when?string("dd.MM.yyyy HH:mm:ss")}.
[#elseif eventType == 'ON RETRY']
Batch was automatically restarted at ${when?string("dd.MM.yyyy HH:mm:ss")}.
[/#if]
[#assign lastId = 0]
[#-- loop over entries--]
[#list resultsFromData as item]
[#-- Check if new group starts --]
[#if item.sqtsActionId != lastId]
[#-- New group: output blank line, ID and name --]
[#if item?index != 0]
[/#if]
${item.sqtsDiagramGroupName}/${item.actionName}(${item.sqtsActionId?c})
[#assign lastId = item.sqtsActionId]
[/#if]
${item.startDt?string("dd.MM.yyyy HH:mm:ss")} - ${item.endDt?string("dd.MM.yyyy HH:mm:ss")} State: "${statusCodes[item.status]}" [#if item.errorCode = "0"][#if item.executionMode = "K"]Skipped[#else]Solved[/#if] by ${item.insertUser}[#else]Inserted by ${item.insertUser}
Error: ${item.errorCode}[/#if] ${item["statusText"]!" "?replace("\n", " \n")}
[/#list]
Batch Dependencies
When editing batches, there is a Batch Dependencies window at the bottom right. Here you define which batches (Dependent From) this batch depends on, or in other words, which batches this batch should react to, or not start or abort.
There are four different reasons when a batch can influence the start of this batch:
- If Scheduled: When the Dependent From batch is supposed to start at the same time (Next Run date) or earlier but has not yet started
- If Running: When there is currently a running instance of the Dependent From batch
- If Error: When there is currently an instance of the Dependent From batch in an error state
- If Missing: When the last execution of the Dependent From batch is smaller than the planned execution (Next run date) of this batch
Activities in case of one of the conditions
- Wait: This batch waits until this dependency no longer exists
- Start: This batch can start from the perspective of this dependency
- Skip: This batch should no longer be executed
A batch waits when a condition of the dependent batches results in Wait A batch is skipped when a condition of the dependent batches results in Skip A batch is started when all conditions in all dependent batches result in Start
Self-dependency A batch can also be made dependent on itself. In this case, the If Scheduled and If Missing conditions are ignored.