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 WHERE TBSPACE
= '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.
1. Define the procedure
CREATE OR REPLACE PROCEDURE WORK_SCHEMA. proc_insert(
sno IN NUMBER,
name IN VARCHAR2(30))
AS
BEGIN
insert into master_tab values(sno, name, 'usa', '040');
END;
2.Schedule the task to run at 10:45 AM every day
db2 "CALL SYSPROC.ADMIN_TASK_ADD('INSERT JOB',NULL,NULL,NULL,
'45 10 * * *', 'WORK_SCHEMA', 'proc_insert',
'VALUES(101,'sam')',NULL,
NULL)”
DBMS_JOB.SUBMIT procedure
:Creata a job definition and store in the database
The SUBMIT procedure creates a job definition and stores it in the
database.
A job consists of a job identifier, the stored procedure to be
executed, when the job is first executed, and a date function that
calculates the next date and time for the job to be run.
Syntax:
SUBMIT—(--job--,'--what--',
--next_date-,'--interval---',--no_parse-')
Usage :
Example :- Lets take the same proc_insert stored
procedure and schedule it using DBMS_JOB.SUBMIT procedure.
SET SERVEROUTPUT ON
DB20000I The SET SERVEROUTPUT command completed successfully.
BEGIN
DECLARE jobid INTEGER;
CALL DBMS_JOB.SUBMIT(jobid,'CALL proc_insert(101,'sam');',
TO_DATE('10:45',
'HH24:MI'), 'SYSDATE + 1');
CALL DBMS_OUTPUT.PUT_LINE('jobid: ' || jobid);
END
These
posts are my opinions and do not necessarily represent IBM’s
positions, strategies, or opinions
Was
this useful? Would you like to know more about scheduling jobs?
Happy Reading!!
Cheers
Ramya Yeleswarapu
mailto :- ramyeles@in[dot]ibm[dot]com