Skip to main content
Skip table of contents

Jobs

You are looking at an older version of the documentation. The latest version is found here.

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, OUT jobid biginteger NOT NULL RESULT)

This procedure takes the following parameters:

ParameterDescription
scriptSQL script (only for the SQL job type); see below for more details
descriptionJob description (only for the SQL job type)
parallelRunsAllowedNumber of parallel runs the job can have; If the number is reached, further starts of the particular job will be ignored. Default: 1
retryCounterNumber of retry attempts before the job is set to FAILED state
retryDelayDelay 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
jobNameJob's unique identifier

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

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 Data 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 Data Virtuality Studio

SQL jobs can be created with the Data 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, 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:

ValueDescription
-1Jobs will not be initialized at all
0All jobs will be initialized at once; default
>0Minutes 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"
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.