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:
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:
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 |
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 withBEGIN
andEND
at runtime; To change a
script
, use the following stored procedure using a givenjobId
. For changing other values thanscript
, please usechangeJobParameters()
.CODESYSADMIN.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:
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:
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:
set "JAVA_OPTS=%JAVA_OPTS% -DjobDelay=2"
2. Linux:
JAVA_OPTS="$JAVA_OPTS -DjobDelay=2"