PolarDB:DBMS_JOB

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) cluster running kernel version 1.1.7 or later. Run the following statement to check your version:

  SHOW polar_version;

• The DBMS_JOB extension enabled on your cluster. The extension cannot be created manually — contact Alibaba Cloud to enable it.

• A privileged database account. For instructions, see Create a database account.

Usage notes

• Only privileged accounts can use the DBMS_JOB extension.

• The DBMS_JOB extension can only be created in the postgres database. To schedule jobs for other databases, configure cross-database jobs from the postgres database. See Run a scheduled job across databases.

Set up test data

The examples throughout this page use the following table and stored procedure. Run these statements in your database before following along.

Create the jobrun table:

CREATE TABLE jobrun (
    id              serial               NOT NULL PRIMARY KEY,
    runtime         VARCHAR2(40)
);

Create the job_proc stored procedure:

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 a scheduled job

Use DBMS_JOB.SUBMIT to create a scheduled job.

Syntax

SUBMIT(job OUT BINARY_INTEGER, what VARCHAR2
[, next_date DATE [, interval VARCHAR2 ]])

Parameters

Example: Submit job_proc to run every minute, starting immediately:

DECLARE
   jobid           INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)');
END;

-- Causes a syntax error:
DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'TRUNC(sysdate,'mi') + 1/(24*60)');

-- Correct — use dollar-quoting:
DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, $$TRUNC(sysdate,'mi') + 1/(24*60)$$);

Query scheduled jobs

Query all jobs created by the current user:

SELECT * FROM sys.user_jobs;

The sys.user_jobs view contains the following columns: job, jobloguser, job_user, database, job_created, job_changed, last_date, last_sec, next_date, next_sec, total_time, broken, interval, failures, what, and instance.

Modify a scheduled job

Use the following subprograms to update a job's stored procedure, start time, or interval.

CHANGE(job BINARY_INTEGER, what VARCHAR2, next_date DATE, interval VARCHAR2)

BEGIN
    DBMS_JOB.CHANGE(1, NULL, TO_DATE('29-DEC-20','DD-MON-YY'), $$Trunc(sysdate,'hh') + (60+15)/(24*60)$$);
END;

INTERVAL(job BINARY_INTEGER, interval VARCHAR2)

BEGIN
    DBMS_JOB.INTERVAL(1,'TRUNC(sysdate) + 1 + 1/(24)');
END;

NEXT_DATE(job BINARY_INTEGER, next_date DATE)

BEGIN
    DBMS_JOB.NEXT_DATE(1, TO_DATE('30-DEC-20','DD-MON-YY'));
END;

WHAT(job BINARY_INTEGER, what VARCHAR2)

BEGIN
    DBMS_JOB.WHAT(1,'job_proc2');
END;

Stop and resume a scheduled job

Use DBMS_JOB.BROKEN to pause or resume a scheduled job.

Syntax

BROKEN(job BINARY_INTEGER, broken BOOLEAN [, next_date DATE ])

Parameters

Example: Stop job 1:

BEGIN
    DBMS_JOB.BROKEN(1, true);
END;

Example: Resume job 1:

BEGIN
    DBMS_JOB.BROKEN(1, false);
END;

Force a scheduled job to run

Use DBMS_JOB.RUN to run a job immediately, regardless of its scheduled time or broken status.

Syntax

RUN(job BINARY_INTEGER)

Parameters

Example: Force job 1 to run:

BEGIN
    DBMS_JOB.RUN(1);
END;

ERROR: syntax error at end of input
CONTEXT: polar-spl function dbms_job._run_job(integer,boolean) line 151 at RAISE line 547 of package body

Delete a scheduled job

Use DBMS_JOB.REMOVE to delete a scheduled job from the queue.

Syntax

REMOVE(job BINARY_INTEGER)

Parameters

Example: Delete job 1:

BEGIN
    DBMS_JOB.REMOVE(1);
END;

Query execution records

Query the execution history of all scheduled jobs:

SELECT * FROM dbmsjob.pga_joblog;

The dbmsjob.pga_joblog view contains the following columns: jlgid, jlgjobid, jlgstatus, jlgstart, and jlgduration.

Run a scheduled job across databases

The DBMS_JOB extension can only be created in the postgres database. To schedule jobs against other databases, configure the job in postgres and pass the target database name as a fifth argument to DBMS_JOB.SUBMIT.

The following example sets up a job in postgres that inserts one record per minute into a table in a separate database named test. For instructions on creating a database, see Create a database.

1. In the test database, create the target table and stored procedure. Create the jobrun table:

   CREATE TABLE public.jobrun (
       id       serial NOT NULL PRIMARY KEY,
       runtime  VARCHAR2(40)
   );

   Create the job_proc stored procedure:

   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. In the postgres database, submit the cross-database job. Pass the target database name (test) as the fifth argument:

   DECLARE
      jobid           INTEGER;
   BEGIN
      DBMS_JOB.SUBMIT(jobid,'job_proc;', SYSDATE, 'SYSDATE + 1/(24 * 60)','test');
   END;

3. In the postgres database, verify the job was created and check its execution records. Query the job:

   SELECT * FROM sys.user_jobs;

   Example output:

   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;

   Example output:

   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. In the test database, verify the inserted data:

   SELECT * FROM jobrun;

   Example output:

   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

BEGIN
    DBMS_JOB.CHANGE(1, NULL, SYSDATE, 'SYSDATE + 1/(24 * 30)', 'test');
END;

Delete the DBMS_JOB extension

DROP EXTENSION dbms_job CASCADE;