PolarDB provides the DBMS_JOB package which is used to schedule and manage scheduled jobs. This topic describes how to create, manage, and delete a scheduled job.

Prerequisites

  • The DBMS_JOB package cannot be enabled by using command lines or the PolarDB console. If you want to enable the package, Submit a ticket to contact technical support.
  • The kernel version of your PolarDB cluster is 1.1.7 or later. For more information about how to check the kernel version, see Release notes.

Usage notes

  • Only privileged accounts can create and use the DBMS_JOB package. For more information about how to create a privileged account, see Create database accounts.
  • If you have an existing dbms_job extension, delete and reinstall it by following the instructions described in this topic. The following syntax can be used to delete the extension:
    drop extension dbms_job;
    Warning If you delete the extension, the scheduled jobs that are related to the extension are deleted. Back up your data before you delete the extension.
  • You can create the dbms_job extension in only the postgres database. If you want to configure scheduled jobs for other databases, configure cross-database scheduled jobs in the postgres database. For more information, see Run a scheduled job across databases.

Prepare test data

Note The test data applies only to the examples that are provided in this topic.
Create a table named jobrun for testing, as shown in the following example:
CREATE TABLE jobrun (
    id              serial               NOT NULL PRIMARY KEY,
    runtime         VARCHAR2(40)
);
Create a stored procedure named job_proc, as shown in the following example:
CREATE PROCEDURE job_proc
IS
BEGIN
    INSERT INTO jobrun(runtime) VALUES ('job_proc run at ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
END;

Create or delete the dbms_job extension

You can use the DBMS_JOB package only after you create the dbms_job extension. The following list describes the syntax that you can use to create or delete the extension:
  • Create the dbms_job extension.
    CREATE EXTENSION IF NOT EXISTS dbms_job;
  • Delete the dbms_job extension.
    DROP EXTENSION dbms_job CASCADE;
    Warning If you delete the extension, all the existing user jobs that are related to the extension are deleted.

Create a scheduled job

Syntax
SUBMIT(job OUT BINARY_INTEGER, what VARCHAR2
[, next_date DATE [, interval VARCHAR2 ]])
Table 1. Parameters
Parameter Description
job The jobid value that you pass. After you pass the jobid value, the ID of the submitted job is returned. The job ID is automatically generated and is unique for each job.
what The name of the stored procedure that you want to call. You must specify this parameter. The job_proc stored procedure is used in the examples in this topic.
next_date The start time of the scheduled job. If you do not specify this parameter, the current time is automatically used.
interval The interval at which the scheduled job is run. For more information, see Table 2.
Table 2. Interval description
Execution interval Example
Every minute 
TRUNC(sysdate,'mi') + 1/(24*60)
Every day The system runs the job at 01:00:00 every day:
TRUNC(sysdate) + 1 + 1/(24)
Every week The system runs the job at 01:00:00 on every Monday:
TRUNC(next_day(sysdate,'monday')) + 1/24
Every month The system runs the job at 01:00:00 on the first day of every month:
TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24
Every quarter The system runs the job at 01:00:00 on the first day of every quarter:
TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
Every year The system runs the job at 01:00:00 on January 1 every year:
ADD_MONTHS(trunc(sysdate,'yyyy'), 12) + 1/24
Fixed point in time The system runs the job at 08:10:00 every morning:
Trunc(Sysdate+1) + (8*60+10)/24*60
Fixed interval The system runs the job at the fifteenth minute of every hour, such as 08:15:00, 09:15:00, and 10:15:00. 
Trunc(sysdate,'hh') + (60+15)/(24*60)
Call the job_proc stored procedure to create a scheduled job, as shown in the following example:
DECLARE
   jobid           INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)');
END;
Note The use of single quotation marks when creating scheduled jobs will create nested quotations in the statement. This will result in a syntax error. The following code provides an example:
DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'TRUNC(sysdate,'mi') + 1/(24*60)');

You must enclose the corresponding parameter values in $$ symbols to prevent this error, as shown in the following example: 

DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, $$TRUNC(sysdate,'mi') + 1/(24*60)$$);

Modify the content, start time, and execution interval of a scheduled job

Syntax
CHANGE(job BINARY_INTEGER what VARCHAR2, next_date DATE,interval VARCHAR2)
Table 3. Parameters
Parameter Description
job The job ID. For more information about how to check the job ID, see Query scheduled jobs.
what The name of the stored procedure that you want to call.
Note If you want to use the current value, set this parameter to NULL.
next_date The start time of the scheduled job.
Note If you want to use the current value, set this parameter to NULL.
interval The interval at which the scheduled job is run. For more information, see Table 2.
Note If you want to use the current value, set this parameter to NULL.
Assume that you want to modify the scheduled job whose job ID is 1. You want the stored procedure to remain unchanged. You also want to change the start time of the scheduled job to December 29, 2020 and run the job at the fifteenth minute of every hour. Example:
BEGIN
    DBMS_JOB.CHANGE(1,NULL,TO_DATE('29-DEC-20','DD-MON-YY'),$$Trunc(sysdate,'hh') + (60+15)/(24*60)$$);
END;

Modify the execution interval of a scheduled job

Syntax
INTERVAL(job BINARY_INTEGER, interval VARCHAR2)
Table 4. Parameters
Parameter Description
job The job ID. For more information about how to check the job ID, see Query scheduled jobs.
interval The interval at which the scheduled job is run. For more information, see Table 2.
Assume that you want to modify the scheduled job whose job ID is 1. You want to set the interval to run the job at 01:00:00 every day. Example:
BEGIN
    DBMS_JOB.INTERVAL(1,'TRUNC(sysdate) + 1 + 1/(24)');
END;

Modify the start time of a scheduled job

Syntax
NEXT_DATE(job BINARY_INTEGER, next_date DATE)
Table 5. Parameters
Parameter Description
job The job ID. For more information about how to check the job ID, see Query scheduled jobs.
next_date The start time of the scheduled job.
Assume that you want to modify the scheduled job whose job ID is 1. You want to set the start time of the scheduled job to December 30, 2020. Example:
BEGIN
    DBMS_JOB.NEXT_DATE(1, TO_DATE('30-DEC-20','DD-MON-YY'));
END;

Modify the content of a scheduled job

Syntax
WHAT(job BINARY_INTEGER, what VARCHAR2)
Table 6. Parameters
Parameter Description
job The job ID. For more information about how to check the job ID, see Query scheduled jobs.
what The name of the stored procedure that you want to call.
Assume that you want to modify the scheduled job whose job ID is 1. You want to change the stored procedure to job_proc2. Example:
BEGIN
    DBMS_JOB.WHAT(1,'job_proc2');
END;

Stop and start a scheduled job

Syntax
BROKEN(job BINARY_INTEGER, broken BOOLEAN [, next_date DATE ])
Table 7. Parameters
Parameter Description
job The job ID. For more information about how to check the job ID, see Query scheduled jobs.
broken The status of the scheduled job. Valid values:
  • true: The status of the scheduled job is abnormal. If the status of a scheduled job is abnormal, the scheduled job is stopped. You can run the dbms_job.run command to force a scheduled job whose status is abnormal to run.
  • false: The status of the scheduled job is normal.
next_date The start time of the scheduled job. If you do not specify this parameter, the current time is automatically used.
Specify the status of the scheduled job whose job ID is 1 as abnormal. Example:
BEGIN
    DBMS_JOB.BROKEN(1,true);
END;
Specify the status of the scheduled job whose job ID is 1 as normal. Example:
BEGIN
    DBMS_JOB.BROKEN(1,false);
END;

Force a scheduled job to run

Syntax
RUN(job BINARY_INTEGER)
Table 8. Parameter
Parameter Description
job The job ID. For more information about how to check the job ID, see Query scheduled jobs.
Force the scheduled job whose job ID is 1 to run. Example:
BEGIN
    DBMS_JOB.RUN(1);
END;

Delete a scheduled task

Syntax
REMOVE(job BINARY_INTEGER)
Table 9. Parameter
Parameter Description
job The job ID. For more information about how to check the job ID, see Query scheduled jobs.
Delete the scheduled job whose job ID is 1. Example:
BEGIN
    DBMS_JOB.REMOVE(1);
END;

Query scheduled jobs

You can execute the following statement to query your scheduled jobs:
select * from sys.dba_jobs;

Query the execution records of a scheduled job

You can execute the following statement to query the execution records of your scheduled jobs:
select * from dbmsjob.pga_joblog;

Run a scheduled job across databases

The DBMS_JOB package applies only to the postgres database. If you want to configure scheduled jobs for other databases, configure cross-database scheduled jobs in the postgres database.

An example is used to describe how to configure a cross-database scheduled job. In this example, the DBMS_JOB package is configured in the postgres database. A scheduled job is configured to run in a database named test. In this example, you want to insert one data record per minute into a table in the test database. For more information about how to create a database, see Create a database.

  1. Create a table named jobrun and a stored procedure named job_proc in the test database.
    • Execute the following statement to create a table named jobrun:
      CREATE TABLE jobrun (
    id              serial               NOT NULL PRIMARY KEY,
    runtime         VARCHAR2(40)
);
    • Execute the following statement to create a stored procedure named job_proc:
      CREATE PROCEDURE job_proc
IS
BEGIN
    INSERT INTO jobrun(runtime) VALUES ('job_proc run at ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
END;
  2. Create a scheduled job in the postgres database.

    You must add the database where you want to run the scheduled job to the DBMS_JOB.SUBMIT() function. In this example, the database where the scheduled job runs is named test. For more information about other parameters, see Create a scheduled job.

    Example:
    DECLARE
   jobid           INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)','test');
END;
  3. Query the status and execution records of the scheduled job in the postgres database.
    • Query the scheduled job:
      select * from sys.dba_jobs;
      The following result is returned:
       job | jobloguser | job_user | database |           job_created            |           job_changed            |            last_date             | last_sec |         next_date         | next_sec |   total_time    | broken |                 interval                 | failures |                                                      what                                                       | instance
-----+------------+----------+----------+----------------------------------+----------------------------------+----------------------------------+----------+---------------------------+----------+-----------------+--------+------------------------------------------+----------+-----------------------------------------------------------------------------------------------------------------+----------
   1 | DBUSER     | dbuser   | postgres | 29-OCT-20 02:38:49.478494 +00:00 | 29-OCT-20 02:38:49.478494 +00:00 | 29-OCT-20 02:51:12.025001 +00:00 | 02:51:12 | 29-OCT-20 02:53:12 +00:00 | 02:53:12 | 00:00:00.243224 | N      | BEGIN return SYSDATE + 1/(24 * 30); END; |        0 |  BEGIN EXECUTE IMMEDIATE 'SELECT dbmsjob.dbms_job_internal_job_link(''BEGIN job_proc; END;'', ''test'');' ; END |        0
    • Query the execution records:
      select * from dbmsjob.pga_joblog;
      The following result is returned:
       jlgid | jlgjobid | jlgstatus |             jlgstart             |   jlgduration   
-------+----------+-----------+----------------------------------+-----------------
     1 |        1 | s         | 29-OCT-20 02:38:49.762995 +00:00 | 00:00:00.017495
     2 |        1 | s         | 29-OCT-20 02:39:50.061113 +00:00 | 00:00:00.016463
     3 |        1 | s         | 29-OCT-20 02:40:50.062331 +00:00 | 00:00:00.016244
  4. Query the data in the jobrun table in the test database.

    Execute the following statement:

    select * from jobrun;
    The following result is returned:
     id |               runtime               
----+-------------------------------------
  1 | job_proc run at 2020-10-29 02:38:50
  2 | job_proc run at 2020-10-29 02:39:50
  3 | job_proc run at 2020-10-29 02:40:50
Note If you want to modify the information about a cross-database scheduled job, you must add the name of the database where you want to run the scheduled job when you modify the function. For example, if you want to change the execution interval from every minute in the preceding example to every two minutes, execute the following statement:
BEGIN
    DBMS_JOB.CHANGE(1,NULL,SYSDATE,'SYSDATE + 1/(24 * 30)','test');
END;