2
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 seconds.
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'
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 ATSA 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
Was this useful? Would you like to know more about scheduling jobs?
Happy Reading!!
Cheers
mailto :- ramyeles@in[dot]ibm[dot]com
