Scheduling Daemon Jobs

4.3 Scheduling Daemon Jobs

The daemon's behavior is controlled by a YAML configuration file that defines the jobs to be scheduled.

The name of this YAML file or the schedule file is scheduler.yaml. The SQLcl daemon reads this file to load and schedule jobs.

Learn about the schedule file's location, its structure, and how the daemon actively monitors the file to reload jobs.

4.3.1 Schedule File Location

The Database Tools (dbtools) home directory contains the schedule file.

The schedule file location is:

${HOME}/.dbtools/schedules/scheduler.yaml

When the daemon is started for the first time, it automatically creates the schedule file at this location, with a commented-out sample job.

Default content on first run:

cat ~/.dbtools/schedules/scheduler.yaml                                                                                                                       
jobs:
# - name: job-example
#   cron: 0/2 * * * * ? *
#   connection: named-conn-example
#   payload: "@/path/to/script.sql arg1 arg2 arg3"

4.3.2 Schedule File Structure

The scheduler.yaml file contains a top-level key (jobs) that points to a list of job definitions.

Each job entry in a jobs list is an object with the following keys:

name (Required): A unique identifier for the job. This name is also used for the job's log file.
cron (Required): A Quartz-compatible cron expression with seven fields (from seconds to year) that defines the schedule.
connection (Optional): A saved SQLcl connection (for example, a connection created using conn -save <name> -savepwd).
Using a saved connection allows the job to run without interactive password prompts. If omitted, the job runs using the default connection (/ as sysdba).
payload (Required): A valid SQL, PL/SQL, or SQLcl-specific command or script to be executed by the daemon.
Payload examples:
- Inline SQL or SQLcl command: Useful for simple, single-line operations.
```
payload: desc employees
```
- Script file with arguments: Useful for enhancing reusability and flexibility across use cases.
```
payload: "@/path/to/generate_report.sql 2024 Q1"
```
  Notice the quotes around the string starting with @. Using this string without quotes causes a syntax error because the @ character is reserved for future use according to the YAML specification.
- Multiline payload using YAML literal block: Ideal for longer SQL or PL/SQL scripts defined directly in YAML.
```
payload: |
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Job started');
    -- Add more PL/SQL logic here
  END;
  /
```

4.3.3 Live Reload of Jobs

The daemon actively monitors the scheduler.yaml file for changes.

When the file is modified and saved, the daemon performs the following actions without requiring a restart:

Calculates a checksum of each job definition to detect changes.
Adds new jobs if any.
Reschedules jobs that were modified.
Unschedules jobs that were removed.
Leaves unchanged jobs as is.