In the previous lesson, we already created a replication job and a materialization job. This session will introduce a third option (SQL jobs) and elaborate on job management.
Via SQL jobs, you can schedule arbitrary SQL statements to be executed. As an example, we would like to delete data older than ten years from our previously created table
First, we compose the corresponding SQL statement in an SQL editor:
DELETE FROM dwh.sales_history WHERE modified_date <= TIMESTAMPADD(SQL_TSI_YEAR, -10, CURDATE());;
We can run it once ( or ) to verify the query works as expected. To schedule the same statement as a recurring job, click the “Schedule a script” icon from the SQL editor’s toolbar (). Provide a description for the job, click “Next” to choose a schedule, and finish the schedule.
Your SQL job is now set up.
You can open the job management via the main toolbar ():
For each job, certain status information is displayed, such as the last execution status, current status and next execution. A click on the expand icon () will show schedules assigned to the corresponding job.
- Configure additional job parameters: Right-click on a job, select “Edit job” and open “Advanced options”. Here you can fine-tune the behaviour of the job, such as automatic retries, timeouts or parallel runs.
- Configure email notification: You can be notified about a certain outcome of a specific job, or for all jobs globally. To configure per-job email notification, right-click on the job and select “Configure email notification”. For global notifications, open “Service / Global Job Email Notifications”.
As a prerequisite, you must have configured email sending before via “Window / Preferences / Server preferences / SMTP Configuration”.
- Show history: A list of previous runs of the job, and historical context information such as the query plan or data lineage.
- Schedules: You can add more schedules, delete, edit or disable existing ones. More details on schedules below.
Data Virtuality Server supports two types of schedules: Time-based schedules and dependent schedules.
Time-based schedules trigger a job at a given time or in a certain interval. For complex conditions, a cron expression can be specified. Dependent schedules allow building chains where a job is triggered based on the outcome of another job. This is useful if there are dependencies between jobs, e.g. a materialization job depends on a replication job to finish first, before reloading the materialization.
More details on scheduling can be found in the documentation.