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.
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.
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. |
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. |
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 || '_%'
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.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@
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.