• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Stored Procedures
  5. Advanced Packages
  6. DBMS_JOB

DBMS_JOB

Related Interfaces

Table 1 lists all interfaces supported by the DBMS_JOB package.

Table 1 DBMS_JOB

API

Description

DBMS_JOB.SUBMIT

Submits a job to the job queue. The job number is automatically generated by the system.

DBMS_JOB.ISUBMIT

Submits a job to the job queue. The job number is specified by the user.

DBMS_JOB.REMOVE

Removes a job from the job queue by job number.

DBMS_JOB.BROKEN

Disables or enables job execution.

DBMS_JOB.CHANGE

Modifies user-definable attributes of a job, including the job content, next-execution time, and execution interval.

DBMS_JOB.WHAT

Modifies the job description for a specified job.

DBMS_JOB.NEXT_DATE

Modifies the next-execution time attribute of a job.

DBMS_JOB.INTERVAL

Modifies the execution interval attribute of a job.

  • DBMS_JOB.SUBMIT

    The stored procedure SUBMIT submits a job provided by the system.

    A prototype of the DBMS_JOB.SUBMIT function is as follows:

    DMBS_JOB.SUBMIT(
    what         IN   TEXT,
    next_date    IN   TIMESTAMP DEFAULT sysdate,
    job_interval IN   TEXT  DEFAULT 'null',
    job          OUT  INTEGER);
    NOTE:

    When a job is created (using DBMS_JOB), the system binds the current database and the user name to the job by default. This function can be invoked by using call or select. If you invoke this function by using select, there is no need to specify output parameters. To invoke this function within a stored procedure, use perform.

    Table 2 DBMS_JOB.SUBMIT interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    what

    text

    IN

    No

    SQL statement to be executed. One or multiple DMLs, anonymous blocks, and SQL statements that invoke stored procedures, or all three combined are supported.

    next_date

    timestamp

    IN

    No

    Specifies the next time the job will be executed. The default value is the current system time (sysdate). If the specified time has past, the job is executed at the time it is submitted.

    interval

    text

    IN

    Yes

    Calculates the next time to execute the job. It can be an interval expression, or sysdate followed by a numeric value, for example, sysdate+1.0/24. If this parameter is left blank or set to null, the job will be executed only once, and the job status will change to 'd' afterward.

    job

    integer

    OUT

    No

    Specifies the job number. The value ranges from 1 to 32767. When dbms.submit is invoked using select, this parameter can be skipped.

    For example:

    select DBMS_JOB.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1');
    
    select DBMS_JOB.SUBMIT('call pro_xxx();', to_date('20180101','yyyymmdd'),'sysdate+1.0/24');
    
    CALL DBMS_JOB.SUBMIT('INSERT INTO T_JOB  VALUES(1);  call pro_1(); call pro_2();', add_months(to_date('201701','yyyymm'),1), 'date_trunc(''day'',SYSDATE) + 1 +(8*60+30.0)/(24*60)' ,:jobid);
  • DBMS_JOB.ISUBMIT

    ISUBMIT has the same syntax function as SUBMIT, but the first parameter of ISUBMIT is an input parameter, that is, a specified job number. In contrast, that last parameter of SUBMIT is an output parameter, indicating the job number automatically generated by the system.

    For example:

    CALL dbms_job.isubmit(101, 'insert_msg_statistic1;', sysdate, 'sysdate+3.0/24');
  • DBMS_JOB.REMOVE

    The stored procedure REMOVE deletes a specified job.

    A prototype of the DBMS_JOB.REMOVE function is as follows:

    REMOVE(job  IN  INTEGER);
    Table 3 DBMS_JOB.REMOVE interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    For example:

    CALL dbms_job.remove(101);
  • DBMS_JOB.BROKEN

    The stored procedure BROKEN sets the broken flag of a job.

    A prototype of the DBMS_JOB.BROKEN function is as follows:

    DMBS_JOB.BROKEN(
    job          IN   INTEGER,
    broken       IN   BOOLEAN,
    next_date    IN   TIMESTAMP  DEFAULT  sysdate);
    Table 4 DBMS_JOB.BROKEN interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    broken

    Boolean

    IN

    No

    Specifies the status flag, true for broken and false for not broken. Setting this parameter to true or false updates the current job. If the parameter is left blank, the job status remains unchanged.

    next_date

    timestamp

    IN

    Yes

    Specifies the next execution time. The default is the current system time. If broken is set to true, next_date is updated to '4000-1-1'. If broken is false and next_date is not empty, next_date is updated for the job. If next_date is empty, it will not be updated. This parameter can be omitted, and its default value will be used in this case.

    For example:

    CALL dbms_job.broken(101, true);
    CALL dbms_job.broken(101, false, sysdate);
  • DBMS_JOB.CHANGE

    The stored procedure CHANGE modifies user-definable attributes of a job, including the job content, next-execution time, and execution interval.

    A prototype of the DBMS_JOB.CHANGE function is as follows:

    DMBS_JOB.CHANGE(
    job          IN   INTEGER,
    what         IN   TEXT,
    next_date    IN   TIMESTAMP,
    interval     IN   TEXT);
    Table 5 DBMS_JOB.CHANGE interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    what

    text

    IN

    Yes

    Specifies the name of the stored procedure or SQL statement block that is executed. If this parameter is left blank, the system does not update the what parameter for the specified job. Otherwise, the system updates the what parameter for the specified job.

    next_date

    timestamp

    IN

    Yes

    Specifies the next execution time. If this parameter is left blank, the system does not update the next_date parameter for the specified job. Otherwise, the system updates the next_date parameter for the specified job.

    interval

    text

    IN

    Yes

    Specifies the time expression for calculating the next time the job will be executed. If this parameter is left blank, the system does not update the interval parameter for the specified job. Otherwise, the system updates the interval parameter for the specified job after necessary validity check. If this parameter is set to null, the job will be executed only once, and the job status will change to 'd' afterward.

    For example:

    CALL dbms_job.change(101, 'call userproc();', sysdate, 'sysdate + 1.0/1440');
    CALL dbms_job.change(101, 'insert into tbl_a values(sysdate);', sysdate, 'sysdate + 1.0/1440');
  • DBMS_JOB.WHAT

    The stored procedure WHAT modifies the procedures to be executed by a specified job.

    A prototype of the DBMS_JOB.WHAT function is as follows:

    DMBS_JOB.WHAT(
    job             IN     INTEGER,
    what            IN     TEXT);
    Table 6 DBMS_JOB.WHAT interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    what

    text

    IN

    No

    Specifies the name of the stored procedure or SQL statement block that is executed.

    NOTE:

    If the value specified by the what parameter is one or multiple executable SQL statements, program blocks, or stored procedures, this procedure can be executed successfully; otherwise, it will fail to be executed.

    If the what parameter is a simple statement such as insert and update, a schema name must be added in front of the table name.

    For example:

    CALL dbms_job.what(101, 'call userproc();');
    CALL dbms_job.what(101, 'insert into tbl_a values(sysdate);');
  • DBMS_JOB.NEXT_DATE

    The stored procedure NEXT_DATE modifies the next-execution time attribute of a job.

    A prototype of the DBMS_JOB.NEXT_DATE function is as follows:

    DMBS_JOB.NEXT_DATE(
    job          IN    INTEGER,
    next_date    IN    TIMESTAMP);
    Table 7 DBMS_JOB.NEXT_DATE interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    next_date

    timestamp

    IN

    No

    Specifies the next execution time.

    NOTE:

    If the specified next_date value is earlier than the current date, the job is executed once immediately.

    For example:

    CALL dbms_job.next_date(101, sysdate);
  • DBMS_JOB.INTERVAL

    The stored procedure INTERVAL modifies the execution interval attribute of a job.

    A prototype of the DBMS_JOB.INTERVAL function is as follows:

    DMBS_JOB.INTERVAL(
    job              IN   INTEGER,
    interval         IN   TEXT);
    Table 8 DBMS_JOB.INTERVAL interface parameters

    Parameter

    Type

    Input/Output Parameter

    Can Be Empty

    Description

    job

    integer

    IN

    No

    Specifies the job number.

    interval

    text

    IN

    Yes

    Specifies the time expression for calculating the next time the job will be executed. If this parameter is left blank or set to null, the job will be executed only once, and the job status will change to 'd' afterward. interval must be a valid time or interval type.

    For example:

    CALL dbms_job.interval(101, 'sysdate + 1.0/1440');
    NOTE:

    For a job that is currently running (that is, job_status is 'r'), it is not allowed to use remove, change, next_date, what, or interval to delete or modify job parameters.

Constraints

  1. After a new job is created, this job belongs to the current coordinator only, that is, this job can be scheduled and executed only on the current coordinator. Other coordinators will not schedule or execute this job. All coordinators can query, modify, and delete jobs created on other CNs.
  2. Create, update, and delete jobs only using the procedures provided by the DBMS_JOB package. These procedures synchronize job information between different CNs and associate primary keys between the pg_job and pg_job_proc tables. If you use DML statements to add, delete, or modify records in the pg_job table, job information will become inconsistent between CNs and system tables may fail to be associated, compromising internal job management.
  3. Each job created by a user is bound to a CN. If the CN fails while a job is being executed, the job status cannot be updated in real time and will stay at 'r'. The job status will be updated to 's' only after the CN recovers. When a CN fails, all jobs on this CN cannot be scheduled or executed until the CN is restored manually, or deleted and then replaced.
  4. For each job, the hosting CN updates the real-time job information (including the job status, last execution start time, last execution end time, next execution start time, the number of execution failures [if any]) to the pg_job table, and synchronizes the information to other CNs, ensuring consistent job information between different CNs. In the case of CN failures, job information synchronization is reattempted by the hosting CNs, which increases job execution time. Although job information fails to be synchronized between CNs, job information can still be properly updated in the pg_job table on the hosting CNs, and jobs can be executed successfully. After a CN recovers, job information such as job execution time and status in its pg_job table may be incorrect and will be updated only after the jobs are executed again on related CNs.
  5. For each job, a thread is established to execute it. If multiple jobs are triggered concurrently as scheduled, the system will need some time to start the required threads, resulting in a latency of 0.1 ms in job execution.