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

Prerequisites

  • You can perform the following steps to manually enable the DBMS_JOB package:
    1. Log on to the PolarDB console and click the name of the cluster for which you want to enable the DBMS_JOB package.
    2. In the left-side navigation pane, choose Settings and Management > Parameters.
    3. Add dbms_job to the extension list of the shared_preload_libraries parameter.
  • 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

  • Only privileged accounts can use the DBMS_JOB package. For more information about how to create a privileged account, see Create database accounts.
  • Due to security reasons, you cannot manually create the dbms_job extension. To create the dbms_job extension, go to the Quota Center. Click Apply in the Actions column corresponding to polardb_pg_dbms_job.
  • 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:
    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.
    drop extension dbms_job;
  • 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;

Delete the DBMS_JOB package

Warning If you delete the DBMS_JOB package, all the existing user jobs that are related to the DBMS_JOB package are deleted.
The following syntax can be used to delete the DBMS_JOB package:
DROP EXTENSION dbms_job CASCADE;

Create a scheduled job

Syntax
SUBMIT(job OUT BINARY_INTEGER, what VARCHAR2
[, next_date DATE [, interval VARCHAR2 ]])
Table 1. Parameters
ParameterDescription
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.
whatThe 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_dateThe start time of the scheduled job. If you do not specify this parameter, the current time is automatically used.
intervalThe interval at which the scheduled job is run. For more information, see Interval description.
Table 2. Interval description
Execution intervalExample
Every minute
TRUNC(sysdate,'mi') + 1/(24*60)
Every dayThe system runs the job at 01:00:00 every day:
TRUNC(sysdate) + 1 + 1/(24)
Every weekThe system runs the job at 01:00:00 on every Monday:
TRUNC(next_day(sysdate,'monday')) + 1/24
Every monthThe system runs the job at 01:00:00 on the first day of every month:
TRUNC(LAST_DAY(SYSDATE)) + 1 + 1/24
Every quarterThe system runs the job at 01:00:00 on the first day of every quarter:
TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
Every yearThe 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 intervalThe 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 If you use single quotation marks when you create scheduled jobs, nested quotations are created in the statement. This can result in a syntax error. The following sample statement 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
ParameterDescription
jobThe job ID. For more information about how to check the job ID, see Query scheduled jobs.
whatThe 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_dateThe start time of the scheduled job.
Note If you want to use the current value, set this parameter to NULL.
intervalThe interval at which the scheduled job is run. For more information, see Interval description.
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 with the stored procedure 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. The following sample statement provides an 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
ParameterDescription
jobThe job ID. For more information about how to check the job ID, see Query scheduled jobs.
intervalThe interval at which the scheduled job is run. For more information, see Interval description.
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. The following sample statement provides an 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
ParameterDescription
jobThe job ID. For more information about how to check the job ID, see Query scheduled jobs.
next_dateThe 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. The following sample statement provides an 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
ParameterDescription
jobThe job ID. For more information about how to check the job ID, see Query scheduled jobs.
whatThe 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. The following sample statement provides an 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
ParameterDescription
jobThe job ID. For more information about how to check the job ID, see Query scheduled jobs.
brokenThe state of the scheduled job. Valid values:
  • true: The state of the scheduled job is abnormal. If the state 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 state is abnormal to run.
  • false: The state of the scheduled job is normal.
next_dateThe start time of the scheduled job. If you do not specify this parameter, the current time is automatically used.
Specify the state of the scheduled job whose job ID is 1 as abnormal. The following sample statement provides an example:
BEGIN
    DBMS_JOB.BROKEN(1,true);
END;
Specify the state of the scheduled job whose job ID is 1 as normal. The following sample statement provides an example:
BEGIN
    DBMS_JOB.BROKEN(1,false);
END;

Force a scheduled job to run

Syntax
RUN(job BINARY_INTEGER)
Table 8. Parameters
ParameterDescription
jobThe 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. The following sample statement provides an example:
BEGIN
    DBMS_JOB.RUN(1);
END;

Delete a scheduled task

Syntax
REMOVE(job BINARY_INTEGER)
Table 9. Parameters
ParameterDescription
jobThe 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. The following sample statement provides an example:
BEGIN
    DBMS_JOB.REMOVE(1);
END;

Query scheduled jobs

You can execute the following statement to query the list of scheduled jobs created by the current user:
select * from sys.user_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 public.jobrun (
          id              serial               NOT NULL PRIMARY KEY,
          runtime         VARCHAR2(40)
      );
    • Execute the following statement to create a stored procedure named job_proc:
      CREATE PROCEDURE public.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 in which you want to run the scheduled job to the DBMS_JOB.SUBMIT() function. In this example, the database in which the scheduled job runs is named test. For more information about other parameters, see Create a scheduled job.

    The following sample statement provides an example:
    DECLARE
       jobid           INTEGER;
    BEGIN
       DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)','test');
    END;
  3. Query the state and execution records of the scheduled job in the postgres database.
    • Query the scheduled job:
      select * from sys.user_jobs;
      The following output 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 output 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 output 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 in which 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;