Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-3.x)/ Stored Procedures/ Advanced Packages/ Secondary Encapsulation APIs (Recommended)/ DBE_SCHEDULER

Updated on 2025-03-13 GMT+08:00

View PDF

DBE_SCHEDULER

APIs

The advanced package DBE_SCHEDULER supports more flexible creation of scheduled jobs through scheduling and programing. For details about all the supported APIs, see Table 1.

DBE_SCHEDULER does not support scheduled jobs for synchronizing data between nodes. To create scheduled jobs for multiple nodes, use DBE_TASK.

**Table 1** DBE_SCHEDULER
API	Description
DBE_SCHEDULER.CREATE_JOB	Creates a scheduled job.
DBE_SCHEDULER.DROP_JOB	Deletes a scheduled job.
DBE_SCHEDULER.DROP_SINGLE_JOB	Deletes a single scheduled job.
DBE_SCHEDULER.SET_ATTRIBUTE	Sets object attributes.
DBE_SCHEDULER.RUN_JOB	Executes a scheduled job.
DBE_SCHEDULER.RUN_BACKEND_JOB	Runs a scheduled job in the backend.
DBE_SCHEDULER.RUN_FOREGROUND_JOB	Runs a scheduled job in the frontend.
DBE_SCHEDULER.STOP_JOB	Stops a scheduled job.
DBE_SCHEDULER.STOP_SINGLE_JOB	Stops a single scheduled job.
DBE_SCHEDULER.GENERATE_JOB_NAME	Generates the name of a scheduled job.
DBE_SCHEDULER.CREATE_PROGRAM	Creates a program.
DBE_SCHEDULER.DEFINE_PROGRAM_ARGUMENT	Defines program parameters.
DBE_SCHEDULER.DROP_PROGRAM	Deletes a program.
DBE_SCHEDULER.DROP_SINGLE_PROGRAM	Deletes a single program.
DBE_SCHEDULER.SET_JOB_ARGUMENT_VALUE	Sets the parameters of a scheduled job.
DBE_SCHEDULER.CREATE_SCHEDULE	Creates a schedule.
DBE_SCHEDULER.DROP_SCHEDULE	Deletes a schedule.
DBE_SCHEDULER.DROP_SINGLE_SCHEDULE	Deletes a single schedule.
DBE_SCHEDULER.CREATE_JOB_CLASS	Creates the class of a scheduled job.
DBE_SCHEDULER.DROP_JOB_CLASS	Deletes the class of a scheduled job.
DBE_SCHEDULER.DROP_SINGLE_JOB_CLASS	Deletes the class of a single scheduled job.
DBE_SCHEDULER.GRANT_USER_AUTHORIZATION	Grants special permissions to a user.
DBE_SCHEDULER.REVOKE_USER_AUTHORIZATION	Revokes special permissions from a user.
DBE_SCHEDULER.CREATE_CREDENTIAL	Creates a certificate.
DBE_SCHEDULER.DROP_CREDENTIAL	Destroys a certificate.
DBE_SCHEDULER.ENABLE	Enables an object.
DBE_SCHEDULER.ENABLE_SINGLE	Enables a single object.
DBE_SCHEDULER.DISABLE	Disables an object.
DBE_SCHEDULER.DISABLE_SINGLE	Disables a single object.
DBE_SCHEDULER.EVAL_CALENDAR_STRING	Analyzes character strings in the Calendar format.
DBE_SCHEDULER.EVALUATE_CALENDAR_STRING	Analyzes character strings in the Calendar format.

DBE_SCHEDULER.CREATE_JOB

Creates a scheduled job.

The prototypes of the DBE_SCHEDULER.CREATE_JOB function are as follows:

      
       
         
         
           -- Scheduled jobs of an inline schedule and a program.
DBE_SCHEDULER.CREATE_JOB(
job_name TEXT,
job_type TEXT,
job_action TEXT,
number_of_arguments INTEGER             DEFAULT 0,
start_date TIMESTAMP WITH TIME ZONE     DEFAULT NULL,
repeat_interval TEXT                    DEFAULT NULL,
end_date TIMESTAMP WITH TIME ZONE       DEFAULT NULL,
job_class TEXT            DEFAULT 'DEFAULT_JOB_CLASS',
enabled BOOLEAN                         DEFAULT FALSE,
auto_drop BOOLEAN                       DEFAULT TRUE,
comments TEXT                           DEFAULT NULL,
credential_name TEXT                    DEFAULT NULL,
destination_name TEXT                   DEFAULT NULL
)

-- Reference the created scheduled jobs of the schedule and the program.
DBE_SCHEDULER.CREATE_JOB(
job_name TEXT,
program_name TEXT,
schedule_name TEXT,
job_class TEXT            DEFAULT 'DEFAULT_JOB_CLASS',
enabled BOOLEAN                         DEFAULT FALSE,
auto_drop BOOLEAN                       DEFAULT TRUE,
comments TEXT                           DEFAULT NULL,
job_style TEXT                          DEFAULT 'REGULAR',
credential_name TEXT                    DEFAULT NULL,
destination_name TEXT                   DEFAULT NULL
)

-- Reference the created program and the scheduled job of the inline schedule.
DBE_SCHEDULER.CREATE_JOB(
job_name text,
program_name TEXT,
start_date TIMESTAMP WITH TIME ZONE     DEFAULT NULL,
repeat_interval TEXT                    DEFAULT NULL,
end_date TIMESTAMP WITH TIME ZONE       DEFAULT NULL,
job_class TEXT            DEFAULT 'DEFAULT_JOB_CLASS',
enabled BOOLEAN                         DEFAULT FALSE,
auto_drop BOOLEAN                       DEFAULT TRUE,
comments TEXT                           DEFAULT NULL,
job_style TEXT                          DEFAULT 'REGULAR',
credential_name TEXT                    DEFAULT NULL,
destination_name TEXT                   DEFAULT NULL
)

-- Reference the created schedule and the scheduled job of the inline program.
DBE_SCHEDULER.CREATE_JOB(
job_name TEXT,
schedule_name TEXT,
job_type TEXT,
job_action TEXT,
number_of_arguments INTEGER             DEFAULT 0,
job_class TEXT             DEFAULT 'DEFAULT_JOB_CLASS',
enabled BOOLEAN                         DEFAULT FALSE,
auto_drop BOOLEAN                       DEFAULT TRUE,
comments TEXT                           DEFAULT NULL,
credential_name TEXT                    DEFAULT NULL,
destination_name TEXT                   DEFAULT NULL
)

          

        

      
     

The scheduled job created through DBE_SCHEDULER does not conflict with the scheduled job in DBE_TASK.

The scheduled job created by DBE_SCHEDULER generates the corresponding job_id. However, job_id is meaningless.

**Table 2** DBE_SCHEDULER.CREATE_JOB API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
job_name	text	IN	No	Name of a scheduled job.
job_type	text	IN	No	Inline program type of a scheduled job. The options are as follows: 'PLSQL_BLOCK': fast anonymous stored procedure. 'STORED_PROCEDURE': stored procedure that is saved. 'EXTERNAL_SCRIPT': external script.
job_action	text	IN	No	Content executed by an inline program of a scheduled job.
number_of_arguments	integer	IN	No	Number of inline program parameters of a scheduled job.
program_name	text	IN	No	Name of the program referenced by a scheduled job.
start_date	timestamp with time zone	IN	Yes	Inline scheduling start time of a scheduled job.
repeat_interval	text	IN	Yes	Inline scheduling period of a scheduled job.
end_date	timestamp with time zone	IN	Yes	Inline scheduling expiration time of a scheduled job.
schedule_name	text	IN	No	Name of the schedule referenced by a scheduled job.
job_class	text	IN	No	Class name of a scheduled job.
enabled	boolean	IN	No	Status of a scheduled job.
auto_drop	boolean	IN	No	Automatic deletion of a scheduled job.
comments	text	IN	Yes	Comments.
job_style	text	IN	No	Behavior pattern of a scheduled job. Only 'REGULAR' is supported.
credential_name	text	IN	Yes	Certificate name of a scheduled job.
destination_name	text	IN	Yes	Target name of a scheduled job.

Example:

       
            gaussdb=# CALL DBE_SCHEDULER.create_program('program1', 'STORED_PROCEDURE', 'SELECT pg_sleep(1);', 3, false, 'test');
 create_program 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule1', NULL, 'sysdate', NULL, 'test');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_job(job_name=>'job1', program_name=>'program1', schedule_name=>'schedule1');
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_schedule('schedule1');
 drop_schedule 
---------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_program('program1', false);
 drop_program 
--------------

(1 row)

To create a scheduled job of the EXTERNAL_SCRIPT type, the administrator must assign related permissions and certificates and the user who starts the database must have the read permission on the external script.

DBE_SCHEDULER.DROP_JOB

Deletes a scheduled job.

The prototype of the DBE_SCHEDULER.DROP_JOB function is as follows:

     
          DBE_SCHEDULER.drop_job(
job_name text,
force boolean                           default false,
defer boolean                           default false,
commit_semantics text      default 'STOP_ON_FIRST_ERROR'
)

You can specify one or more jobs, or specify a job class in DBE_SCHEDULER.DROP_JOB to delete scheduled jobs.

**Table 3** DBE_SCHEDULER.DROP_JOB API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
job_name	text	IN	No	Name or class of a scheduled job. You can specify one or more scheduled jobs. If you specify multiple scheduled jobs, separate them with commas (,).
force	boolean	IN	No	Specifies whether to delete a scheduled job. true: The current scheduled job is stopped and then deleted. false: The scheduled job fails to be deleted if it is running.
defer	boolean	IN	No	Specifies whether to delete a scheduled job. true: A scheduled job can be deleted after it is complete.
commit_semantics	text	IN	No	Commit rules: 'STOP_ON_FIRST_ERROR': The deletion operation performed before the first error is reported is committed. 'TRANSACTIONAL': Transaction-level commit. The deletion operation performed before an error is reported will be rolled back. 'ABSORB_ERRORS': Attempt to bypass an error and commit the deletion operation that is performed successfully.

Example:

      
           gaussdb=# CALL DBE_SCHEDULER.create_program('program1', 'STORED_PROCEDURE', 'SELECT pg_sleep(1);', 3, false, 'test');
 create_program 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule1', NULL, 'sysdate', NULL, 'test');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_job(job_name=>'job1', program_name=>'program1', schedule_name=>'schedule1');
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_schedule('schedule1');
 drop_schedule 
---------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_program('program1', false);
 drop_program 
--------------

(1 row)

The TRANSACTIONAL option in commit_semantic takes effect only when force is set to false.

DBE_SCHEDULER.DROP_SINGLE_JOB

Deletes a scheduled job.

The prototype of the DBE_SCHEDULER.DROP_SINGLE_JOB function is as follows:

     
          DBE_SCHEDULER.drop_single_job(
job_name text,
force boolean                           default false,
defer boolean                           default false
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_program('program1', 'STORED_PROCEDURE', 'SELECT pg_sleep(1);', 0, false, 'test');
 create_program 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_job('job1', 'program1', '2021-07-20', 'interval ''3 minute''', '2121-07-20', 'DEFAULT_JOB_CLASS', false, false,'test', 'style', NULL, NULL);
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_single_job('job1', false, false);
 drop_single_job 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_program('program1', false);
 drop_program 
--------------

(1 row)

DBE_SCHEDULER.SET_ATTRIBUTE

Modifies the attributes of a scheduled job.

The prototypes of the DBE_SCHEDULER.SET_ATTRIBUTE function are as follows:

     
      
        
        
          DBE_SCHEDULER.set_attribute(
name                    text,
attribute               text,
value                   boolean
)

DBE_SCHEDULER.set_attribute(
name                    text,
attribute               text,
value                   text
)

DBE_SCHEDULER.set_attribute(
name                    text,
attribute               text,
value                   timestamp
)

DBE_SCHEDULER.set_attribute(
name                    text,
attribute               text,
value                   timestamp with time zone
)

DBE_SCHEDULER.set_attribute(
name text,
attribute text,
value text,
value2 text                             default NULL
)

         

       

     
    

name specifies any object in DBE_SCHEDULER.

**Table 4** DBE_SCHEDULER.SET_ATTRIBUTE API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
name	text	IN	No	Object name.
attribute	text	IN	No	Attribute name.
value	boolean/date/timestamp/timestamp with time zone/text	IN	No	Attribute value. The options are as follows: Scheduled-job-related: job_type, job_action, number_of_arguments, start_date, repeat_interval, end_date, job_class, enabled, auto_drop, comments, credential_name, destination_name, program_name, schedule_name, and job_style. Program-related: program_action, program_type, number_of_arguments, and comments. Scheduling-related: start_date, repeat_interval, end_date, and comments.
value2	text	IN	Yes	Additional attribute value. Reserved parameter bit. Currently, the target attribute with extra attribute values is not supported.

Example:

      
           gaussdb=# CALL DBE_SCHEDULER.create_program('program1', 'STORED_PROCEDURE', 'SELECT pg_sleep(1);', 3, false, 'test');
 create_program 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.set_attribute('program1', 'number_of_arguments', 0);
 set_attribute 
---------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.set_attribute('program1', 'program_type', 'STORED_PROCEDURE');
 set_attribute 
---------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_program('program1', false);
 drop_program 
--------------

(1 row)

Do not use DBE_SCHEDULER.SET_ATTRIBUTE to leave the parameters empty.

The object name cannot be changed using DBE_SCHEDULER.SET_ATTRIBUTE.

Inline objects cannot be changed by DBE_SCHEDULER.SET_ATTRIBUTE.

DBE_SCHEDULER.RUN_JOB

Executes a scheduled job.

The prototype of the DBE_SCHEDULER.RUN_JOB function is as follows:

     
          DBE_SCHEDULER.run_job(
job_name text,
use_current_session boolean             default true
)

DBE_SCHEDULER.RUN_JOB is used to run scheduled jobs immediately. It is independent of the scheduling of scheduled jobs and can even run at the same time.

**Table 5** DBE_SCHEDULER.RUN_JOB API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
job_name	text	IN	No	Name of a scheduled job. You can specify one or more scheduled jobs. If you specify multiple scheduled jobs, separate them with commas (,).
use_current_session	boolean	IN	No	Specifies whether to run a scheduled job. true: Use the current session to check whether the scheduled job can run properly. false: Start the scheduled job in the backend. The execution result is recorded in logs.

Example:

      
           gaussdb=# SELECT dbe_scheduler.create_job('job1','PLSQL_BLOCK','BEGIN INSERT INTO test1 VALUES(12); end;',0,null,null,null,'DEFAULT_JOB_CLASS',false,false,null,null,null);
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.run_job('job1', false);
 run_job 
---------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

Currently, use_current_session applies only to scheduled jobs whose job_type is set to EXTERNAL_SCRIPT.

DBE_SCHEDULER.RUN_BACKEND_JOB

Runs a scheduled job in the backend.

The prototype of the DBE_SCHEDULER.RUN_BACKEND_JOB function is as follows:

     
          DBE_SCHEDULER.run_backend_job(
job_name text
)

Example:

gaussdb=# SELECT dbe_scheduler.create_job('job1','PLSQL_BLOCK','BEGIN INSERT INTO test1 VALUES(12); end;',0,null,null,null,'DEFAULT_JOB_CLASS',false,false,null,null,null);
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.run_backend_job('job1');
 run_backend_job 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

DBE_SCHEDULER.RUN_FOREGROUND_JOB

Executes a scheduled job in the current session.

Only external jobs can be executed.

Return value: text

The prototype of the DBE_SCHEDULER.RUN_FOREGROUND_JOB function is as follows:

     
          DBE_SCHEDULER.run_foreground_job(
job_name text
)return text

Example:

gaussdb=# CREATE USER test1 IDENTIFIED BY '*********';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
gaussdb=# SELECT DBE_SCHEDULER.create_credential('cre_1', 'test1', '*********');
 create_credential 
-------------------

(1 row)

gaussdb=# SELECT DBE_SCHEDULER.create_job(job_name=>'job1', job_type=>'EXTERNAL_SCRIPT', job_action=>'/usr/bin/pwd', enabled=>true, auto_drop=>false, credential_name => 'cre_1');
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.run_foreground_job('job1');
  run_foreground_job
---------------------------------
 Host key verification failed.\r+

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_credential('cre_1', false);
 drop_credential 
-----------------

(1 row)

gaussdb=# DROP USER test1;
DROP ROLE

DBE_SCHEDULER.STOP_JOB

Stops a scheduled job.

The prototype of the DBE_SCHEDULER.STOP_JOB function is as follows:

     
          DBE_SCHEDULER.stop_job(
job_name text,
force boolean                           default false,
commit_semantics text                   default 'STOP_ON_FIRST_ERROR'
)

**Table 6** DBE_SCHEDULER.STOP_JOB API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
job_name	text	IN	No	Name or class of a scheduled job. You can specify one or more scheduled jobs. If you specify multiple scheduled jobs, separate them with commas (,).
force	boolean	IN	No	Specifies whether to delete a scheduled job. true: The scheduler sends a termination signal to end the job thread immediately. false: The scheduler attempts to use the interrupt signal to terminate the scheduled job thread.
commit_semantics	text	IN	No	Commit rules: 'STOP_ON_FIRST_ERROR': The interrupt operation performed before the first error is reported is committed. 'ABSORB_ERRORS': The system attempts to bypass an error and commit the interrupt operation that is performed successfully.

Example:

gaussdb=# SELECT dbe_scheduler.create_job('job1','PLSQL_BLOCK','BEGIN INSERT INTO test1 VALUES(12); end;',0,null,null,null,'DEFAULT_JOB_CLASS',false,false,null,null,null);
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.stop_job('job1', true, 'STOP_ON_FIRST_ERROR');
 stop_job 
----------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

DBE_SCHEDULER.STOP_SINGLE_JOB

Stops a single scheduled job.

The prototype of the DBE_SCHEDULER.STOP_SINGLE_JOB function is as follows:

     
          DBE_SCHEDULER.stop_single_job(
job_name text,
force boolean                           default false
)

Example:

gaussdb=# SELECT dbe_scheduler.create_job('job1','PLSQL_BLOCK','BEGIN INSERT INTO test1 VALUES(12); end;',0,null,null,null,'DEFAULT_JOB_CLASS',false,false,null,null,null);
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.stop_single_job('job1', true);
 stop_single_job 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

DBE_SCHEDULER.GENERATE_JOB_NAME

Generates the name of a scheduled job.

The prototype of the DBE_SCHEDULER.GENERATE_JOB_NAME function is as follows:

     
          DBE_SCHEDULER.generate_job_name(
prefix text                             default 'JOB$_'
)return text

**Table 7** DBE_SCHEDULER.GENERATE_JOB_NAME API parameters
Parameter	Type	Input/Output Parameter	Can Be Empty	Description
prefix	text	IN	No	Prefix of the generated name. The default value is 'JOB$_'. Scheduled jobs that are repeatedly executed are named as follows: job$_1, job$_2, job$_3 ...

Example:

gaussdb=# CALL DBE_SCHEDULER.generate_job_name();
 generate_job_name 
-------------------
 JOB$_1
(1 row)

gaussdb=# CALL DBE_SCHEDULER.generate_job_name();
 generate_job_name 
-------------------
 JOB$_2
(1 row)

gaussdb=# CALL DBE_SCHEDULER.generate_job_name('job');
 generate_job_name 
-------------------
 job3
(1 row)

gaussdb=# CALL DBE_SCHEDULER.generate_job_name('job');
 generate_job_name 
-------------------
 job4
(1 row)

When DBE_SCHEDULER.GENERATE_JOB_NAME is executed for the first time, a temporary sequence is created in public to store the sequence number of the current name. A common user does not have the create permission in public. Therefore, if a common user calls the function for the first time in the current database, the function fails to be called. In this case, you need to grant the create permission in public to the common user or call the API as a user with the create permission to create a temporary sequence.

DBE_SCHEDULER.CREATE_PROGRAM

Creates a program.

The prototype of the DBE_SCHEDULER.CREATE_PROGRAM function is as follows:

     
          DBE_SCHEDULER.create_program(
program_name text,
program_type text,
program_action text,
number_of_arguments integer             default 0,
enabled boolean                         default false,
comments text                           default NULL
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_program('program1', 'STORED_PROCEDURE', 'SELECT pg_sleep(1);', 3, false, 'test');
 create_program 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_program('program1', false);
 drop_program 
--------------

(1 row)

DBE_SCHEDULER.DEFINE_PROGRAM_ARGUMENT

Defines program parameters.

An API with the default value default_value will not convert characters to lowercase letters by default. In this version, characters are case-sensitive.

The prototype of the DBE_SCHEDULER.DEFINE_PROGRAM_ARGUMENT function is as follows:

     
          DBE_SCHEDULER.define_program_argument(
program_name text,
argument_position integer,
argument_name text                      default NULL,
argument_type text,
out_argument boolean                    default false
)

-- With a default value --
DBE_SCHEDULER.define_program_argument(
program_name text,
argument_position integer,
argument_name text                      default NULL,
argument_type text,
default_value text,
out_argument boolean                    default false
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_program('program1', 'STORED_PROCEDURE', 'SELECT pg_sleep(1);', 2, false, 'test');
create_program 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.define_program_argument('program1', 1, 'pa1', 'type1', false);
 define_program_argument 
-------------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.define_program_argument('program1', 1, 'pa1', 'type1', 'value1', false);
 define_program_argument 
-------------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_program('program1', false);
 drop_program 
--------------

(1 row)

DBE_SCHEDULER.DROP_PROGRAM

Deletes a program.

The prototype of the DBE_SCHEDULER.DROP_PROGRAM function is as follows:

     
          DBE_SCHEDULER.drop_program(
program_name text,
force boolean                           default false
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_program('program1', 'STORED_PROCEDURE', 'SELECT pg_sleep(1);', 2, false, 'test');
create_program 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_program('program1', false);
 drop_program 
--------------

(1 row)

DBE_SCHEDULER.DROP_SINGLE_PROGRAM

Deletes a single program.

The prototype of the DBE_SCHEDULER.DROP_SINGLE_PROGRAM function is as follows:

     
          DBE_SCHEDULER.drop_single_program(
program_name text,
force boolean                           default false
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_program('program1', 'STORED_PROCEDURE', 'SELECT pg_sleep(1);', 2, false, 'test');
create_program 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_single_program('program1', false);
 drop_single_program 
---------------------

(1 row)

DBE_SCHEDULER.SET_JOB_ARGUMENT_VALUE

Sets the parameters of a scheduled job. argument_value can be left empty.

The prototype of the DBE_SCHEDULER.SET_JOB_ARGUMENT_VALUE function is as follows:

     
          DBE_SCHEDULER.set_job_argument_value(
job_name text,
argument_position integer,
argument_value text
)

DBE_SCHEDULER.set_job_argument_value(
job_name text,
argument_name text,
argument_value text
)

Example:

gaussdb=# CALL dbe_scheduler.create_job('job1','EXTERNAL_SCRIPT','BEGIN INSERT INTO test1 VALUES(12); end;',2,null,null,null,'DEFAULT_JOB_CLASS',false,false,null,null,null);
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.set_job_argument_value('job1', 1, 'value1');
 set_job_argument_value 
------------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

DBE_SCHEDULER.CREATE_SCHEDULE

Creates a schedule.

The prototype of the DBE_SCHEDULER.CREATE_SCHEDULE function is as follows:

     
          DBE_SCHEDULER.create_schedule(
schedule_name text,
start_date timestamp with time zone     default NULL,
repeat_interval text,
end_date timestamp with time zone       default NULL,
comments text                           default NULL
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule1', sysdate, 'sysdate + 3 / (24 * 60 * 60)', null, 'test1');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule2', sysdate, 'FREQ=DAILY; BYHOUR=6;', null, 'test1');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule3', sysdate, 'FREQ=DAILY; BYHOUR=6;');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_schedule('schedule1');
 drop_single_schedule 
----------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_schedule('schedule2', false);
 drop_single_schedule 
----------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_schedule('schedule3', true);
 drop_single_schedule 
----------------------

(1 row)

DBE_SCHEDULER.DROP_SCHEDULE

Deletes a schedule.

The prototype of the DBE_SCHEDULER.DROP_SCHEDULE function is as follows:

     
          DBE_SCHEDULER.drop_schedule(
schedule_name text,
force boolean                           default false
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule1', sysdate, 'sysdate + 3 / (24 * 60 * 60)', null, 'test1');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule2', sysdate, 'FREQ=DAILY; BYHOUR=6;', null, 'test1');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule3', sysdate, 'FREQ=DAILY; BYHOUR=6;');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_schedule('schedule1');
 drop_single_schedule 
----------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_schedule('schedule2', false);
 drop_single_schedule 
----------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_schedule('schedule3', true);
 drop_single_schedule 
----------------------

(1 row)

DBE_SCHEDULER.DROP_SINGLE_SCHEDULE

Deletes a single schedule.

The prototype of the DBE_SCHEDULER.DROP_SINGLE_SCHEDULE function is as follows:

     
          DBE_SCHEDULER.drop_single_schedule(
schedule_name text,
force boolean                           default false
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule1', sysdate, 'sysdate + 3 / (24 * 60 * 60)', null, 'test1');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule2', sysdate, 'FREQ=DAILY; BYHOUR=6;', null, 'test1');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_schedule('schedule3', sysdate, 'FREQ=DAILY; BYHOUR=6;');
 create_schedule 
-----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_single_schedule('schedule1');
 drop_single_schedule 
----------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_single_schedule('schedule2', false);
 drop_single_schedule 
----------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_single_schedule('schedule3', true);
 drop_single_schedule 
----------------------

(1 row)

DBE_SCHEDULER.CREATE_JOB_CLASS

Creates the class of a scheduled job.

The prototype of the DBE_SCHEDULER.CREATE_JOB_CLASS function is as follows:

     
          DBE_SCHEDULER.create_job_class(
job_class_name text,
resource_consumer_group text            default NULL,
service text                            default NULL,
logging_level integer                   default 0,
log_history integer                     default NULL,
comments text                           default NULL
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_job_class(job_class_name => 'jc1', resource_consumer_group => '123');
 create_job_class 
------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job_class('jc1', false);
 drop_job_class 
----------------

(1 row)

DBE_SCHEDULER.DROP_JOB_CLASS

Deletes the class of a scheduled job.

The prototype of the DBE_SCHEDULER.DROP_JOB_CLASS function is as follows:

     
          DBE_SCHEDULER.drop_job_class(
job_class_name text,
force boolean                           default false
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_job_class(job_class_name => 'jc1', resource_consumer_group => '123');
 create_job_class 
------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job_class('jc1', false);
 drop_job_class 
----------------

(1 row)

DBE_SCHEDULER.DROP_SINGLE_JOB_CLASS

Deletes the class of a single scheduled job.

The prototype of the DBE_SCHEDULER.DROP_SINGLE_JOB_CLASS function is as follows:

     
          DBE_SCHEDULER.drop_single_job_class(
job_class_name text,
force boolean                           default false
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_job_class(job_class_name => 'jc1', resource_consumer_group => '123');
 create_job_class 
------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_single_job_class('jc1', false);
 drop_single_job_class 
-----------------------

(1 row)

DBE_SCHEDULER.GRANT_USER_AUTHORIZATION

Grants the scheduled job permissions to the database user. The user who calls this function must have the SYSADMIN permission.

The prototype of the DBE_SCHEDULER.GRANT_USER_AUTHORIZATION function is as follows:

     
          DBE_SCHEDULER.grant_user_authorization(
username                text,
privilege               text
)

Example:

gaussdb=# CREATE USER user1 PASSWORD '1*s*****';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
gaussdb=# CALL DBE_SCHEDULER.grant_user_authorization('user1', 'create job');
 grant_user_authorization 
--------------------------

(1 row)
gaussdb=# DROP USER user1;
DROP ROLE

DBE_SCHEDULER.REVOKE_USER_AUTHORIZATION

Revokes the scheduled job permissions from the database user. The user who calls this function must have the SYSADMIN permission.

The prototype of the DBE_SCHEDULER.REVOKE_USER_AUTHORIZATION function is as follows:

     
          DBE_SCHEDULER.revoke_user_authorization(
username                text,
privilege               text
)

Example:

gaussdb=# CREATE USER user1 PASSWORD '1*s*****';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
gaussdb=# CALL DBE_SCHEDULER.grant_user_authorization('user1', 'CREATE JOB');
 grant_user_authorization 
--------------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.revoke_user_authorization('user1', 'CREATE JOB');
 revoke_user_authorization 
---------------------------

(1 row)
gaussdb=# DROP USER user1;
DROP ROLE

DBE_SCHEDULER.CREATE_CREDENTIAL

Creates an authorization certificate. The user who calls this function must have the SYSADMIN permission.

The prototype of the DBE_SCHEDULER.CREATE_CREDENTIAL function is as follows:

     
          DBE_SCHEDULER.create_credential(
credential_name         text,
username                text,
password                text            default NULL,
database_role           text            default NULL,
windows_domain          text            default NULL,
comments                text            default NULL
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_credential('cre_1', 'user1', '');
 create_credential 
-------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_credential('cre_1', false);
 drop_credential 
-----------------

(1 row)

The password parameter of DBE_SCHEDULER.CREATE_CREDENTIAL must be set to NULL or '******'. This parameter is used only for compatibility and does not indicate any actual meaning. Do not use the OS username corresponding to the installation user to create a certificate.

DBE_SCHEDULER.DROP_CREDENTIAL

Destroys an authorization certificate. The user who calls this function must have the SYSADMIN permission.

The prototype of the DBE_SCHEDULER.DROP_CREDENTIAL function is as follows:

     
          DBE_SCHEDULER.drop_credential(
credential_name         text,
force boolean default false
)

Example:

gaussdb=# CALL DBE_SCHEDULER.create_credential('cre_1', 'user1', '');
 create_credential 
-------------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_credential('cre_1', false);
 drop_credential 
-----------------

(1 row)

DBE_SCHEDULER.ENABLE

Enables an object.

The prototype of the DBE_SCHEDULER.ENABLE function is as follows:

     
          DBE_SCHEDULER.enable(
name text,
commit_semantics text                   default 'STOP_ON_FIRST_ERROR'
)

Example:

gaussdb=# CALL dbe_scheduler.create_job('job1','PLSQL_BLOCK','BEGIN INSERT INTO test1 VALUES(12); end;',0,null,null,null,'DEFAULT_JOB_CLASS',false,false,null,null,null);
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_program('program1', 'stored_procedure', 'INSERT INTO tb_job_test(key) VALUES(null);', 0, false, '');
 create_program 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.enable('job1');
 enable 
--------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.enable('program1', 'STOP_ON_FIRST_ERROR');
 enable 
--------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_program('program1', false);
 drop_program 
--------------

(1 row)

DBE_SCHEDULER.ENABLE_SINGLE

Enables a single object.

The prototype of the DBE_SCHEDULER.ENABLE_SINGLE function is as follows:

     
          DBE_SCHEDULER.enable_single(
name text
)

Example:

gaussdb=# CALL dbe_scheduler.create_job('job1','PLSQL_BLOCK','BEGIN INSERT INTO test1 VALUES(12); end;',0,null,null,null,'DEFAULT_JOB_CLASS',false,false,null,null,null);
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.enable_single('job1');
 enable_single 
---------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

DBE_SCHEDULER.DISABLE

Disables multiple objects. The value of name is a character string separated by commas (,). Each character string separated by commas (,) is an object.

The prototype of the DBE_SCHEDULER.DISABLE function is as follows:

     
          DBE_SCHEDULER.disable(
name text,
force boolean                           default false,
commit_semantics text                   default 'STOP_ON_FIRST_ERROR'
)

Example:

gaussdb=# CALL dbe_scheduler.create_job('job1','PLSQL_BLOCK','BEGIN INSERT INTO test1 VALUES(12); end;',0,null,null,null,'DEFAULT_JOB_CLASS',false,false,null,null,null);
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.create_program('program1', 'stored_procedure', 'INSERT INTO tb_job_test(key) VALUES(null);', 0, false, '');
 create_program 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.disable('job1');
 disable 
---------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.disable('program1', false, 'STOP_ON_FIRST_ERROR');
 disable 
---------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_program('program1', false);
 drop_program 
--------------

(1 row)

DBE_SCHEDULER.DISABLE_SINGLE

Disables a single object.

The prototype of the DBE_SCHEDULER.DISABLE_SINGLE function is as follows:

     
          DBE_SCHEDULER.disable_single(
name text,
force boolean                           default false
)

Example:

gaussdb=# CALL dbe_scheduler.create_job('job1','PLSQL_BLOCK','BEGIN INSERT INTO test1 VALUES(12); end;',0,null,null,null,'DEFAULT_JOB_CLASS',false,false,null,null,null);
 create_job 
------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.disable_single('job1', false);
 disable_single 
----------------

(1 row)

gaussdb=# CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
 drop_job 
----------

(1 row)

DBE_SCHEDULER.EVAL_CALENDAR_STRING

Analyzes the scheduling job period.

Return type: timestamp with time zone

The prototype of the DBE_SCHEDULER.EVAL_CALENDAR_STRING function is as follows:

     
          DBE_SCHEDULER.evaluate_calendar_string(
IN calendar_string text,
IN start_date timestamp with time zone,
IN return_date_after timestamp with time zone
)return timestamp with time zone

Example:

gaussdb=# CALL DBE_SCHEDULER.eval_calendar_string('FREQ=DAILY; BYHOUR=6;', sysdate, sysdate);
  eval_calendar_string  
------------------------
 2023-09-15 06:47:24+08
(1 row)

DBE_SCHEDULER.EVALUATE_CALENDAR_STRING

Analyzes the scheduling job period.

The prototype of the DBE_SCHEDULER.EVALUATE_CALENDAR_STRING function is as follows:

     
          DBE_SCHEDULER.evaluate_calendar_string(
IN calendar_string text,
IN start_date timestamp with time zone,
IN return_date_after timestamp with time zone,
OUT next_run_date timestamp with time zone
)return timestamp with time zone

Example:

gaussdb=# CREATE OR REPLACE PROCEDURE pr1(calendar_str text) as
DECLARE
    start_date        timestamp with time zone;
    return_date_after timestamp with time zone;
    next_run_date     timestamp with time zone;
BEGIN
    start_date := '2003-2-1 10:30:00.111111+8'::timestamp with time zone;
    return_date_after := start_date;
    DBE_SCHEDULER.evaluate_calendar_string(
        calendar_str,
        start_date, return_date_after, next_run_date);
    DBE_OUTPUT.PRINT_LINE('next_run_date: ' || next_run_date);
    return_date_after := next_run_date;
END;
/
CREATE PROCEDURE

gaussdb=# CALL pr1('FREQ=hourly;INTERVAL=2;BYHOUR=6,10;BYMINUTE=0;BYSECOND=0');
next_run_date: 2003-02-02 06:00:00+08
 pr1 
-----

(1 row)

Parent topic: Secondary Encapsulation APIs (Recommended)

Previous topic: DBE_MATCH

Next topic: DBE_APPLICATION_INFO