Information Management India - Bloggers Guild

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

CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP

MANAGED BY AUTOMATIC STORAGE

EXTENTSIZE 4

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

Happy Reading!!

Cheers