The administrative task scheduler (ATS)
enables DB2 database servers to automate the execution of tasks. It
also provides a programmable SQL interface, which allows you to build
applications that can take advantage of the administrative task
scheduler.

The administrative task scheduler
manages and runs administrative tasks, which must be encapsulated in
either user-defined or built-in procedures. You can add, update and
remove tasks from the scheduler's task list by using a set of
built-in procedures. You can also monitor the task list and the
status of executed tasks by using administrative views.

DB2 Process responsible for
running scheduled jobs:-

Scheduled tasks are executed by the DB2
autonomic computing daemon, which also hosts the health monitor and
automatic maintenance utilities. This daemon appears in the process
list as db2acd and starts and stops in conjunction with the database
manager. Every five minutes the DB2 autonomic computing daemon checks
for new or updated tasks. To do this, it briefly connects to each
active database and retrieves the new and updated task definitions.
The daemon does not connect to inactive databases. To ensure
scheduled tasks are executed as expected, the database must remain
active and the task's earliest begin time should be at least five
minutes after it is created or updated.

Internally, the
daemon maintains a list of the active tasks. When a task's scheduled
execution time arrives, the daemon connects to the appropriate
database and calls the procedure associated with the task. If the
database is not active, the daemon will not execute the task; it
writes an ADM15502W message in both the administration notification
log and the db2diag.log. If, for some other reason, the daemon fails
to execute the task, an ADM15501W message is written to both the
administration notification log and the db2diag.log. The daemon then
automatically attempts to execute the task every 60 second

The daemon will
never execute a task if a previous instance of the same task is still
outstanding. For example, assume a task is scheduled to run every 5
minutes. If, for some reason, the task takes 7 minutes to complete,
the daemon will not execute another instance of the task at the next
5 minute interval. Instead, the task will run at the 10 minute mark.

The
administrative task scheduler operates independently of the IBM Data
Studio and Database Administration Server (DAS). It is included in
DB2 database servers and is disabled by default. In order to
successfully execute tasks, you must set up the administrative task
scheduler.

Setting up the administrative
task scheduler

Set the DB2_ATS_ENABLE registry
variable to YES, TRUE, 1, or ON

For example:

db2set
DB2_ATS_ENABLE=YES

Create the SYSTOOLSPACE table
space

Like other DB2
administration tools, the administrative task scheduler depends on
the SYSTOOLSPACE table space to store historical data and
configuration information. You can check if the table space already
exists in your database system with the following query:

SELECT
TBSPACE FROM SYSCAT.TABLESPACES WHERETBSPACE
= 'SYSTOOLSPACE'

If your database
does not have this table space, you must create it.Otherwise you
receive an error message when you try to add a task to the
administrative task scheduler:

SQL0204N
"SYSTOOLSPACE" is an undefined name. SQLSTATE=42704

Any user that belongs to the SYSADM or SYSCTRL group has authority to
create this table space. For instructions, refer to "

SYSTOOLSPACE
and SYSTOOLSTMPSPACE table spaces".

For example:

CREATE
TABLESPACE SYSTOOLSPACE IN IBMCATGROUP

MANAGED BY AUTOMATIC STORAGE

EXTENTSIZE 4

Activate
your database. Your database must be active for your tasks to
execute on time. The best way to do this is to use the ACTIVATE
DATABASE command. Alternatively, you can keep a database active if
you maintain at least one database connection at all times.

Results

Once the
administrative task scheduler is set up, the DB2 Autonomic Computing
Daemon starts checking for new or updated tasks by connecting to
active databases every five minutes.

Adding
jobs to ATS

A job is created
by adding a task to the ATS. This can be done in two ways

a)
ADMIN_TASK_ADD procedure to define new scheduled tasks

b) DBMS_JOB
module

Lets see how to
schedule jobs using each one of them.

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:

ADMIN_TASK_ADD--(--name--,--begin_timestamp--,--------------->

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

>--procedure_schema--,--procedure_name--,--procedure_input--,--->

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

Usage :

Example: Consider a stored procedure(proc_insert)
which has to be scheduled to run at 10:45 AM every day.