Jobs

There are different types of jobs for different purposes. In this page, we give an overview of each of the type.

Optimization Jobs

When a system generates an optimization recommendation, it stores the relelvant entry (entries) in the SYSADMIN.RecommendedOptimizations table. One or more jobs can be associated with a recommended optimization. They can be created using one of the appropriate stored procedures listed in Optimization Management. This section contains more detailed description of the optimization jobs.

SQL Jobs

With an SQL job, any SQL script can be executed as a scheduled job. An SQL job can be created using this stored procedure:

CODE

SYSADMIN.createSQLJob(IN script string NOT NULL, IN description string, IN parallelRunsAllowed integer, IN retryCounter integer, IN retryDelay integer, IN runTimeout integer, IN jobName string, IN uuid string, OUT jobid biginteger NOT NULL RESULT)

This procedure takes the following parameters:

Parameter	Description
`script`	SQL script (only for the SQL job type); see below for more details
`description`	Job description (only for the SQL job type)
`parallelRunsAllowed`	Number of parallel runs the job can have; If the number is reached, further starts of the particular job will be ignored. Default: `1`
`retryCounter`	Number of retry attempts before the job is set to `FAILED` state
`retryDelay`	Delay in seconds between the retry attempts
`runTimeout`	Individual job timeout in minutes. If no `runTimeout` is set, configuration set via default value option `JOB_RUN_TIMEOUT` will be considered for the job; if `runTimeout` is reached before the job is finished, the job terminates with the `INTERRUPTED` state
`jobName`	Job's unique identifier
`uuid`	Custom UUID for the created job

It returns a single parameter: jobid which can then be used for changing the job's parameters if needed.

uuid parameter in SYSADMIN.createSQLJob is available since v4.1

script Parameter

Here are some things to keep in mind about the script parameter:

- It may not be null;
- Scheduled jobs only work with scripts that use a single semicolon (';') as a statement separator, even if the script itself contains no BEGIN-END block! The CData Virtuality Server silently surrounds all SQL job scripts with BEGIN and END at runtime;
- To change a script, use the following stored procedure using a given jobId. For changing other values than script, please use changeJobParameters().
  CODE
```
SYSADMIN.changeSQLJob(IN jobid biginteger NOT NULL, IN script string NOT NULL, IN description string, IN parallelRunsAllowed integer, IN retryCounter integer, IN retryDelay integer)
```

Creating SQL Jobs via the CData Virtuality Studio

SQL jobs can be created with the CData Virtuality Studio by clicking the Schedule a script button above the SQL input field in the SQL editor. When selecting only a part of a query, the selected part of the query is used:

Statistics Jobs

These jobs gather statistical information about tables and views to help the optimizer subsystem create optimal query plans. A statistics job can be created using this stored procedure:

CODE

SYSADMIN.createGatherStatisticsJob(IN "datasource" string, IN gatherTableCardinalities boolean NOT NULL, IN gatherColumnCardinalities boolean NOT NULL, IN gatherNetworkStats boolean NOT NULL, IN excludeTables string, IN parallelRunsAllowed integer, IN retryCounter integer, IN retryDelay integer, IN runTimeout integer, IN jobName string, IN uuid string, OUT jobID biginteger NOT NULL RESULT)

Restarting Jobs When the Server is Up

Interrupted jobs can be automatically restarted when the server itself is started.

The default behaviour is to restart all jobs at once when the server is started, but there is a property that can be passed to the system to change this behaviour.

The -DjobDelay property can have the following values:

Value	Description
`-1`	Jobs will not be initialized at all
`0`	All jobs will be initialized at once; default
`>0`	Minutes of initialization delay between the jobs; values greater than 0

The -DjobDelay property can be passed to the system in its initialization through the environment variable JAVA_OPTS. You can find the proper file to add this property to the environment variable in {dvserver}\bin\standalone.conf.props.bat on Windows and {dvserver}/bin/standalone.conf.props on Linux.

In the examples below, the jobs will start at 2 minutes apart from each other:

1. Windows:

BASH

set "JAVA_OPTS=%JAVA_OPTS% -DjobDelay=2"

2. Linux:

BASH

JAVA_OPTS="$JAVA_OPTS -DjobDelay=2"