ADMIN_TASK_ADD procedure - Schedule a new task

The ADMIN_TASK_ADD procedure schedules an administrative task, which is any piece of work that can be encapsulated inside a procedure.

Syntax

The schema is SYSPROC.

Procedure parameters

name

An input argument of type VARCHAR(128) that specifies the name of the task. This argument cannot be NULL.

begin_timestamp

An input argument of type TIMESTAMP that specifies the earliest time a task can begin execution. The value of this argument cannot be in the past, and it cannot be later than end_timestamp.

When task execution begins depends on how this argument and the schedule argument are defined:

If the begin_timestamp argument is not NULL:
- If the schedule argument is NULL, the task execution begins at begin_timestamp.
- If the schedule argument is not NULL, the task execution begins at the next scheduled time at or after begin_timestamp.
If the begin_timestamp argument is NULL:
- If the schedule argument is NULL, the task execution begins immediately.
- If the schedule argument is not NULL, the task execution begins at the next scheduled time.

end_timestamp

An input argument of type TIMESTAMP that specifies the last time that a task can begin execution. The value of this argument cannot be in the past, and it cannot be earlier than begin_timestamp. If the argument is NULL, the task can continue to execute as scheduled indefinitely.

An executing task will not be interrupted at its end_timestamp.

max_invocations

An input argument of type INTEGER that specifies the maximum number of executions allowed for the task. If the argument is NULL, there is no limit to the number of times the task can execute. If the argument is 0, the task will not execute.

This value applies to the schedule if schedule is not NULL.

If both end_timestamp and max_invocations are specified, end_timestamp takes precedence. That is, if the end_timestamp timestamp is reached, even though the number of task executions so far has not reached the value of max_invocations, the task will not be executed again.

schedule

An input argument of type VARCHAR(1024) that specifies a task execution schedule at fixed points in time. If the argument is NULL, the task is not scheduled at fixed points in time.

The schedule string must be specified using the UNIX cron format.

Multiple schedules are not supported.

procedure_schema

An input argument of type VARCHAR(128) that specifies the schema of the procedure that this task will execute. This argument cannot be NULL.

procedure_name

An input argument of type VARCHAR(128) that specifies the name of the procedure that this task will execute. This argument cannot be NULL.

procedure_input

An input argument of type CLOB(2M) that specifies the input arguments of the procedure that this task will execute. This argument must contain an SQL statement that returns one row of data. The returned values will be passed as arguments to the procedure. If this argument is NULL, no arguments are passed to the procedure.

The number of columns returned by the SQL statement must match the total number (and type) of arguments for the procedure and must contain a single row. For output arguments, the value itself is ignored, but should be of the same SQL data type as the procedure requires.

This SQL statement is executed every time the task is executed. If the SQL statement fails, the task's status will be set to NOTRUN and specific SQLCODE information will be recorded. If the statement does not return a result set, does not return a row, returns multiple rows or result sets the task will not be executed. The task's status will be set to NOTRUN and SQLCODE SQL1465N will be set to indicate that this argument is invalid.

If the statement result contains serialized XML parameters, the total size of all XML parameters combined is limited to 256 kilobytes. If the result exceeds this threshold, the task's status will be set to NOTRUN. SQLCODE -302 and SQLSTATE 22001 will be set to indicate that data truncation has occurred.

To view the task's status, use the SYSTOOL.ADMIN_TASK_STATUS view

options

An input argument of type VARCHAR(512). This argument must be NULL.

remarks

An input argument of type VARCHAR(254) that specifies a description of the task. This argument is optional and can be NULL.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the procedure is automatically created.

Usage notes

The SYSTOOLSPACE table space must exist before you call the ADMIN_TASK_ADD procedure. If it does not exist, the procedure will return an SQL0204N error message.

When a task is scheduled, the authorization ID of the current session user is recorded. The scheduler switches to this session authorization ID when the executing the task.

The administrative task scheduler does not support the execution of procedures that perform a database connection without a specified user ID and password. For example, the ADMIN_CMD procedure can be used to perform a LOAD from a database. A connection to the source database is established using the user ID and password provided for the currently connected database. This type of LOAD operation cannot be executed by the task scheduler.

If invalid arguments are passed into the procedure, SQL0171N will be returned. The tokens of the message will indicate which argument is invalid and the name of the procedure.

The task cannot be scheduled for execution until the unit of work is committed and the scheduler has fetched the task definition.

The scheduler checks for new or updated tasks every 5 minutes. To ensure the task executes as expected, the earliest begin time, as defined by the begin_timestamp, end_timestamp and schedule arguments, should be at least 5 minutes after the unit of work commits.

The database must be active on all database partitions to ensure the task can be executed by the scheduler.

In a partitioned database environment, the ADMIN_TASK_ADD procedure can be called from any database partition. The scheduler, however, will execute all tasks from the catalog database partition.

The begin_timestamp, end_timestamp, and schedule are based on the server's time zone. Special attention is required when scheduling a task during the transition period of daylight savings time (DST). If the task is scheduled to run 2:01 AM and it is the time of year when the time springs forward, the task will not run as the clock skips from 2:00 AM to 3:00 AM. If the DST change occurs when the database is activated and there is no task scheduled during the DST change time, the first scheduled task after the DST change will not run. Tasks will run normally from the second task onwards. For example, if no task is scheduled between 2:00 AM to 3:00 AM but there is a task at 3:10 AM which is also the first task after DST change, this task will not run. This is independent of the time after the DST change in which the task is scheduled. For example, whether the first task after DST is scheduled one day or one month after the DST change, the task will not run as long as it is the first task after DST change and the database is kept active the entire time. On the other hand, when the time falls back an hour, tasks that were originally scheduled between 2:00 AM and 3:00 AM will execute twice. The user is responsible for making adjustments for daylight savings time to ensure their required behavior.

The scheduler will always commit after calling the procedure specified by procedure_schema and procedure_name. If a transaction roll back is required, the rollback must occur inside the procedure.

If the task name is not unique, the procedure will fail with SQL0601N.

Example

Example 1: Create a task that performs an online TSM backup daily at 12:00 AM, with immediate effect:

CALL SYSPROC.ADMIN_TASK_ADD
  ( 'DAILY TSM BACKUP',
    CURRENT_TIMESTAMP,
    NULL,
    NULL,
    '0 0 * * *',
    'SYSPROC',
    'ADMIN_CMD',
    'VALUES(''BACKUP DATABASE SALES ONLINE USE TSM WITHOUT PROMPTING'')',
    NULL,
    NULL )

Example 2: Schedule a task to flush an event monitor every hour:

Create an SQL procedure, in the PROD schema, that flushes an event monitor called "em":
```
CREATE PROCEDURE FLUSH_EVENT_MONITOR()
	SPECIFIC FLUSH_EVENT_MONITOR
  	LANGUAGE SQL
  	BEGIN
  	DECLARE stmt VARCHAR(100) ;
  	SET stmt = 'FLUSH EVENT MONITOR em' ;
  	EXECUTE IMMEDIATE stmt ;
  	END
```
Note: The FLUSH EVENT MONITOR SQL statement cannot be called directly in the procedure. However, EXECUTE IMMEDIATE can be used.

Call ADMIN_TASK_ADD to schedule the task:

CALL SYSPROC.ADMIN_TASK_ADD 
	('FLUSH EVENT MONITOR EVERY HOUR',
  	NULL,
   	NULL,
  	NULL,
  	'0 0-23 * * *',
  	'PROD',
  	'FLUSH_EVENT_MONITOR',
  	NULL,
  	NULL,
  	NULL )