DB2 Version 9.7 for Linux, UNIX, and Windows

DBMS_JOB module

The DBMS_JOB module provides procedures for the creation, scheduling, and managing of jobs.

The DBMS_JOB module provides an alternate interface for the Administrative Task Scheduler (ATS). A job is created by adding a task to the ATS. The actual task name is constructed by concatenating the DBMS_JOB.TASK_NAME_PREFIX procedure name with the assigned job identifier, such as SAMPLE_JOB_TASK_1 where 1 is the job identifier.

A job runs a stored procedure which has been previously stored in the database. The SUBMIT procedure is used to create and store a job definition. A job identifier is assigned to every job, along with its associated stored procedure and the attributes describing when and how often the job is run.

On first run of the SUBMIT procedure in a database, the SYSTOOLSPACE table space is created if necessary.

To enable job scheduling for the DBMS_JOB routines, run:

db2set DB2_ATS_ENABLE=1

When and how often a job runs depends upon two interacting parameters - next_date and interval. The next_date parameter is a datetime value that specifies the next date and time when the job is to be executed. The interval parameter is a string that contains a date function that evaluates to a datetime value. Just before any execution of the job, the expression in the interval parameter is evaluated, and the resulting value replaces the next_date value stored with the job. The job is then executed. In this manner, the expression in interval is re-evaluated before each job execution, supplying the next_date date and time for the next execution.

The first run of a scheduled job, as specified by the next_date parameter, should be set at least 5 minutes after the current time, and the interval between running each job should also be at least 5 minutes.

The schema for this module is SYSIBMADM.

The DBMS_JOB module includes the following system-defined routines.

Table 1. System-defined routines available in the DBMS_JOB module
Routine name	Description
BROKEN procedure	Specify that a given job is either broken or not broken.
CHANGE procedure	Change the parameters of the job.
INTERVAL procedure	Set the execution frequency by means of a date function that is recalculated each time the job runs. This value becomes the next date and time for execution.
NEXT_DATE procedure	Set the next date and time when the job is to be run.
REMOVE procedure	Delete the job definition from the database.
RUN procedure	Force execution of a job even if it is marked as broken.
SUBMIT procedure	Create a job and store the job definition in the database.
WHAT procedure	Change the stored procedure run by a job.

Table 2. System-defined constants available in the DBMS_JOB module
Constant name	Description
ANY_INSTANCE	The only supported value for the instance argument for the DBMS_JOB routines.
TASK_NAME_PREFIX	This constant contains the string that is used as the prefix for constructing the task name for the administrative task scheduler.

Usage notes

When the first job is submitted through the DBMS_JOB module for each database, the Administrative Task Scheduler setup is performed:

Create the SYSTOOLSPACE table space if it does not exist;
Create the ATS table and views, such as SYSTOOLS.ADMIN_TASK_LIST.

To list the scheduled jobs, run:

db2 SELECT * FROM systools.admin_task_list
      WHERE name LIKE DBMS_JOB.TASK_NAME_PREFIX || '_%'

To view the status of the job execution, run:

db2 SELECT * FROM systools.admin_task_status
      WHERE name LIKE DBMS_JOB.TASK_NAME_PREFIX || '_%'

Note: The expected value for job identifier is not the value of TASKID that is returned by SYSTOOLS.ADMIN_TASK_LIST. For example, you have the following job list:

NAME                    TASKID                                          
---------------         ------                                          
DBMS_JOB_TASK_2              3                                                
DBMS_JOB_TASK_3              4

If you want to modify DBMS_JOB_TASK_2, you must pass 2 as the job identifier.

Examples

Example 1: The following example uses the stored procedure, job_proc. This stored procedure simply inserts a timestamp into the jobrun table, which contains a single VARCHAR column.

CREATE TABLE jobrun (
    runtime         VARCHAR(40)
)@

CREATE OR REPLACE PROCEDURE job_proc
BEGIN
    INSERT INTO jobrun VALUES ('job_proc run at ' || TO_CHAR(SYSDATE,
        'yyyy-mm-dd hh24:mi:ss'));
END@

This example results in the following output:

CREATE TABLE jobrun ( runtime         VARCHAR(40) )
DB20000I  The SQL command completed successfully.

CREATE OR REPLACE PROCEDURE job_proc
BEGIN
    INSERT INTO jobrun VALUES ('job_proc run at ' || TO_CHAR(SYSDATE,
        'yyyy-mm-dd hh24:mi:ss'));
END
DB20000I  The SQL command completed successfully.