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.
The schema is SYSPROC.
- An input argument of type VARCHAR(128) that specifies the name of the task. This argument cannot be NULL.
- 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.
- If the begin_timestamp argument is not NULL:
- 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.
- 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.
- 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.
- An input argument of type VARCHAR(128) that specifies the schema of the procedure that this task will execute. This argument cannot be NULL.
- An input argument of type VARCHAR(128) that specifies the name of the procedure that this task will execute. This argument cannot be NULL.
- 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
- An input argument of type VARCHAR(512). This argument must be NULL.
- An input argument of type VARCHAR(254) that specifies a description of the task. This argument is optional and can be NULL.
- 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.
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.
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 )
- 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 ; ENDNote: 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 )