DB2 Version 10.1 for Linux, UNIX, and Windows

ADMIN_TASK_UPDATE procedure - Update an existing task

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

Syntax


>>-ADMIN_TASK_UPDATE--(--name--,--begin_timestamp--,------------>

>--end_timestamp--,--max_invocations--,--schedule--,------------>

>--options--,--remarks--)--------------------------------------><

The schema is SYSPROC.

Procedure parameters

name

An input argument of type VARCHAR(128) that specifies the name of an existing 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 parameter and the schedule parameter 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.

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 is an optional argument that can be set to NULL.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the ADMIN_TASK_UPDATE procedure
DATAACCESS authority
DBADM authority
SQLADM authority

Although the statement authorization ID might allow the procedure to be executed, a task cannot be updated unless the current session authorization ID matches the session authorization ID that was recorded when the task was created. Users with SYSADM, SYSCTRL, SYSMAINT, or DBADM can update any existing task. Attempting to update a task that was added by a different user returns SQL0551N.

Default PUBLIC privilege

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

Usage notes

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.

Changes to the task do not take effect until the unit of work is committed and the scheduler has fetched the updated task definition. Leaving the unit of work uncommitted may delay or prevent the execution of the existing task.

The scheduler checks for 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 parameters, 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.

The begin_timestamp, end_timestamp, and schedule are based on the database 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.

When a task is updated, the task's internal invocation counter is reset. To illustrate, consider a recurring task with a max_invocations value of 10. If the task executes 3 times, there are 3 corresponding status records in the ADMIN_TASK_STATUS output. The entries have INVOCATION values of 1, 2, and 3. Now assume the task creator updates the task. This update will reset the internal invocation counter. The original status records remain in intact. Over time, new status records will be created with INVOCATION values of 1, 2, 3, and so on. The BEGIN_TIME can be used to distinguish between the original and updated task execution.