DB2 Version 9.7 for Linux, UNIX, and Windows

SUBMIT procedure - Create a job definition and store it 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


>>-DBMS_JOB.SUBMIT--(--job--,--what----------------------------->

>--+------------------------------------------------+--)-------><
   '-,--next_date--+------------------------------+-'      
                   '-,--interval--+-------------+-'        
                                  '-,--no_parse-'

Parameters

job: An output argument of type DECIMAL(20) that specifies the identifier assigned to the job.
what: An input argument of type VARCHAR(1024) that specifies the name of the dynamically executable SQL statement.
next_date: An optional input argument of type TIMESTAMP(0) that specifies the next date and time when the job is to be run. The default is SYSDATE.
interval: An optional input argument of type VARCHAR(1024) that specifies a date function that, when evaluated, provides the date and time of the execution after the next execution. If interval is set to NULL, then the job is run only once. NULL is the default.
no_parse: An optional input argument of type BOOLEAN. If set to true, do not syntax-check the SQL statement at job creation; instead, perform syntax checking only when the job first executes. If set to false, syntax check the SQL statement at job creation. The default is false.

Authorization

EXECUTE privilege on the DBMS_JOB module.

Examples

Example 1: The following example creates a job using the stored procedure, job_proc. The job will first execute in about 5 minutes, and runs once a day thereafter as set by the interval argument, SYSDATE + 1.

SET SERVEROUTPUT ON@

BEGIN
  DECLARE jobid           INTEGER;
  CALL DBMS_JOB.SUBMIT(jobid,'CALL job_proc();',SYSDATE + 5 minutes, 'SYSDATE + 1');
  CALL DBMS_OUTPUT.PUT_LINE('jobid: ' || jobid);
END@

The output from this command is as follows:

SET SERVEROUTPUT ON
DB20000I  The SET SERVEROUTPUT command completed successfully.

BEGIN
  DECLARE jobid           INTEGER;
  CALL DBMS_JOB.SUBMIT(jobid,'CALL job_proc();',SYSDATE + 5 minutes, 'SYSDATE + 1');
  CALL DBMS_OUTPUT.PUT_LINE('jobid: ' || jobid);
END
DB20000I  The SQL command completed successfully.

jobid: 1