Listing the status of tasks in the administration task scheduler

You can use the ADMIN_TASK_STATUS administrative view to list the status of task execution in the administrative task scheduler.

Before you begin

  • The ADMIN_TASK_STATUS administrative view is created the first time you call the ADMIN_TASK_ADD procedure. You must set up the administrative task scheduler and add a task before you can successfully query this view.
  • You must have SELECT or CONTROL privilege on the ADMIN_TASK_STATUS administrative view.

About this task

When a task is executed, the administrative task scheduler creates a task status record before calling the task's procedure. The status record contains the task name, task ID, invocation number, agent ID and begin time. The status of the task is set to RUNNING. After the task executes, the remaining fields in the status record are updated with values that are based on the outcome of the task.


Restrictions

When you query the ADMIN_TASK_STATUS view, it will only return the tasks that were created by your session authorization ID.

Procedure

  1. Connect to the database.
  2. Issue a query against the ADMIN_TASK_STATUS administrative view.
    For example: 
    SELECT * from SYSTOOLS.ADMIN_TASK_STATUS
    Tip: You can relate the task execution status to the task definition by joining the output from ADMIN_TASK_LIST and ADMIN_TASK_STATUS administrative views on the NAME or TASKID columns.

Results

  • If the scheduler was not able to start executing the task, the STATUS column contains NOTRUN. The BEGIN_TIME column indicates when the task should have started executing and the SQLCODE, SQLSTATE and SQLERRMC columns describe the error that prevented the task from running.
  • If the scheduler started executing the task but the task has not yet completed, the STATUS column contains RUNNING. The BEGIN_TIME column indicates when the task started executing and the END_TIME contains a NULL value. The AGENT_ID value provides information about the agent that is executing the task.
  • If the task execution has completed, the STATUS column contains COMPLETED. The BEGIN_TIME and END_TIME columns contain the actual start and end times. The SQLCODE, SQLSTATE, SQLERRMC, and RC columns might contain informational or error messages.
  • If the scheduler was stopped during the execution of a task, the status remains RUNNING until the scheduler is restarted. When the scheduler starts again, the status is changed to UNKNOWN, because the scheduler cannot determine if the task was completed. The END_TIME indicates the time the administrative task scheduler detected that the task was no longer executing.