DBE_SCHEDULER
Interface Description
The advanced package DBE_SCHEDULER supports more flexible creation of scheduled tasks through scheduling and programing. For details about all the supported interfaces, see Table 1.
DBE_SCHEDULER does not support scheduled tasks for synchronizing data between nodes. To create scheduled tasks for multiple nodes, use DBE_TASK.
|
Interface |
Description |
|---|---|
|
Creates a scheduled task. |
|
|
Deletes a scheduled task. |
|
|
Deletes a single scheduled task. |
|
|
Sets object attributes. |
|
|
Executes a scheduled task. |
|
|
Runs a scheduled task in the background. |
|
|
Runs a scheduled task in the foreground. |
|
|
Stops a scheduled task. |
|
|
Stops a single scheduled task. |
|
|
Generates the name of a scheduled task. |
|
|
Creates a program. |
|
|
Defines program parameters. |
|
|
Deletes a program. |
|
|
Deletes a single program. |
|
|
Sets the parameters of a scheduled task. |
|
|
Creates a schedule. |
|
|
Deletes a schedule. |
|
|
Deletes a single schedule. |
|
|
Creates the class of a scheduled task. |
|
|
Deletes the class of a scheduled task. |
|
|
Deletes the class of a single scheduled task. |
|
|
Grants special permissions to a user. |
|
|
Revokes special permissions from a user. |
|
|
Creates a certificate. |
|
|
Destroys a certificate. |
|
|
Enables an object. |
|
|
Enables a single object. |
|
|
Disables an object. |
|
|
Disables a single object. |
|
|
Analyzes character strings in the Calendar format. |
|
|
Analyzes character strings in the Calendar format. |
- DBE_SCHEDULER.CREATE_JOB
The prototypes of the DBE_SCHEDULER.CREATE_JOB function are as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
-- Scheduled tasks 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 tasks 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 task 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 task 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 task created through DBE_SCHEDULER does not conflict with the scheduled task in DBE_TASK.
The scheduled task created by DBE_SCHEDULER generates the corresponding job_id. However, the job_id is meaningless.
Table 2 DBE_SCHEDULER.CREATE_JOB interface parameters Parameter
Type
Input/Output Parameter
Can Be Empty
Description
job_name
text
IN
No
Name of a scheduled task.
job_type
text
IN
No
Inline program type of a scheduled task. 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 task.
number_of_arguments
integer
IN
No
Number of inline program parameters of a scheduled task.
program_name
text
IN
No
Name of the program referenced by a scheduled task.
start_date
timestamp with time zone
IN
Yes
Inline scheduling start time of a scheduled task.
repeat_interval
text
IN
Yes
Inline scheduling period of a scheduled task.
end_date
timestamp with time zone
IN
Yes
Inline scheduling expiration time of a scheduled task.
schedule_name
text
IN
No
Name of the schedule referenced by a scheduled task.
job_class
text
IN
No
Class name of a scheduled task.
enabled
boolean
IN
No
Status of a scheduled task.
auto_drop
boolean
IN
No
Automatic deletion of a scheduled task.
comments
text
IN
Yes
Comments.
job_style
text
IN
No
Behavior pattern of a scheduled task. Only 'REGULAR' is supported.
credential_name
text
IN
Yes
Certificate name of a scheduled task.
destination_name
text
IN
Yes
Target name of a scheduled task.
Example:1 2 3
CALL DBE_SCHEDULER.create_job(job_name=>'job1', program_name=>'program1', schedule_name=>'schedule1'); CALL DBE_SCHEDULER.create_job(job_name=>'job1', job_type=>'STORED_PROCEDURE', job_action=>'select pg_sleep(1);'); 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);
To create a scheduled task 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
The prototype of the DBE_SCHEDULER.DROP_JOB function is as follows:
1 2 3 4 5 6
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 tasks, or specify a task class in DBE_SCHEDULER.DROP_JOB to delete scheduled tasks.
Table 3 DBE_SCHEDULER.DROP_JOB interface parameters Parameter
Type
Input/Output Parameter
Can Be Empty
Description
job_name
text
IN
No
Name or class of a scheduled task. You can specify one or more scheduled tasks. If you specify multiple scheduled tasks, separate them with commas (,).
force
boolean
IN
No
Specifies whether to delete a scheduled task.
true: The current scheduled task is stopped and then deleted.
false: The scheduled task fails to be deleted if it is running.
defer
boolean
IN
No
Specifies whether to delete a scheduled task.
true: A scheduled task 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:1CALL DBE_SCHEDULER.drop_job('job1', true, false, 'STOP_ON_FIRST_ERROR');
The TRANSACTIONAL option in commit_semantic takes effect only when force is set to false.
- DBE_SCHEDULER.DROP_SINGLE_JOB
The prototype of the DBE_SCHEDULER.DROP_SINGLE_JOB function is as follows:
1 2 3 4 5
DBE_SCHEDULER.drop_single_job( job_name text, force boolean default false, defer boolean default false )
- DBE_SCHEDULER.SET_ATTRIBUTE
Modifies the attributes of a scheduled task.
The prototypes of the DBE_SCHEDULER.SET_ATTRIBUTE function are as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
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_SCHEDULE.
Table 4 DBE_SCHEDULER.SET_ATTRIBUTE interface 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-task-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:1 2
CALL DBE_SCHEDULER.set_attribute('program1', 'number_of_arguments', 0); CALL DBE_SCHEDULER.set_attribute('program1', 'program_type', 'STORED_PROCEDURE');
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
The prototype of the DBE_SCHEDULER.RUN_JOB function is as follows:
1 2 3 4
DBE_SCHEDULER.run_job( job_name text, use_current_session boolean default true )
DBE_SCHEDULER.RUN_JOB is used to run scheduled tasks immediately. It is independent of the scheduling of scheduled tasks and can even run at the same time.
Table 5 DBE_SCHEDULER.RUN_JOB interface parameters Parameter
Type
Input/Output Parameter
Can Be Empty
Description
job_name
text
IN
No
Name of a scheduled task. You can specify one or more scheduled tasks. If you specify multiple scheduled tasks, separate them with commas (,).
use_current_session
boolean
IN
No
Specifies whether to run a scheduled task.- true: Use the current session to check whether the scheduled task can run properly.
- false: Start the scheduled task in the background. The execution result is recorded in logs.
Example:1CALL DBE_SCHEDULER.run_job('job1', false);
Currently, use_current_session applies only to scheduled tasks whose job_type is set to EXTERNAL_SCRIPT.
- DBE_SCHEDULER.RUN_BACKEND_JOB
Runs a scheduled task in the background.
The prototype of the DBE_SCHEDULER.RUN_BACKEND_JOB function is as follows:
1 2 3
DBE_SCHEDULER.run_backend_job( job_name text )
- DBE_SCHEDULER.RUN_FOREGROUND_JOB
Executes a scheduled task in the current session.
Only external tasks can be executed.
Return value: text
The prototype of the DBE_SCHEDULER.RUN_FOREGROUND_JOB function is as follows:
1 2 3
DBE_SCHEDULER.run_foreground_job( job_name text )return text
- DBE_SCHEDULER.STOP_JOB
The prototype of the DBE_SCHEDULER.STOP_JOB function is as follows:
1 2 3 4 5
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 interface parameters Parameter
Type
Input/Output Parameter
Can Be Empty
Description
job_name
text
IN
No
Name or class of a scheduled task. You can specify one or more scheduled tasks. If you specify multiple scheduled tasks, separate them with commas (,).
force
boolean
IN
No
Specifies whether to delete a scheduled task.- true: The scheduler sends a termination signal to end the task thread immediately.
- false: The scheduler attempts to use the interrupt signal to terminate the scheduled task 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.
- DBE_SCHEDULER.STOP_SINGLE_JOB
Stops a single scheduled task.
The prototype of the DBE_SCHEDULER.STOP_SINGLE_JOB function is as follows:
1 2 3 4
DBE_SCHEDULER.stop_single_job( job_name text, force boolean default false )
- DBE_SCHEDULER.GENERATE_JOB_NAME
Generates the name of a scheduled task.
The prototype of the DBE_SCHEDULER.GENERATE_JOB_NAME function is as follows:
1 2 3
DBE_SCHEDULER.generate_job_name( prefix text default 'JOB$_' )return text
Table 7 DBE_SCHEDULER.GENERATE_JOB_NAME interface 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 tasks that are repeatedly executed are named as follows:
job$_1, job$_2, job$_3 ...
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
The prototype of the DBE_SCHEDULER.CREATE_PROGRAM function is as follows:
1 2 3 4 5 6 7 8
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 )
- DBE_SCHEDULER.DEFINE_PROGRAM_ARGUMENT
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
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 )
- DBE_SCHEDULER.DROP_PROGRAM
The prototype of the DBE_SCHEDULER.DROP_PROGRAM function is as follows:
1 2 3 4
DBE_SCHEDULER.drop_program( program_name text, force boolean default false )
- DBE_SCHEDULER.DROP_SINGLE_PROGRAM
The prototype of the DBE_SCHEDULER.DROP_SINGLE_PROGRAM function is as follows:
1 2 3 4
DBE_SCHEDULER.drop_single_program( program_name text, force boolean default false )
- DBE_SCHEDULER.SET_JOB_ARGUMENT_VALUE
Sets the parameters of a scheduled task. The argument_value can be left empty.
The prototype of the DBE_SCHEDULER.SET_JOB_ARGUMENT_VALUE function is as follows:
1 2 3 4 5 6 7 8 9 10 11
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 )
- DBE_SCHEDULER.CREATE_SCHEDULE
The prototype of the DBE_SCHEDULER.CREATE_SCHEDULE function is as follows:
1 2 3 4 5 6 7
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 )
- DBE_SCHEDULER.DROP_SCHEDULE
The prototype of the DBE_SCHEDULER.DROP_SCHEDULE function is as follows:
1 2 3 4
DBE_SCHEDULER.drop_schedule( schedule_name text, force boolean default false )
- DBE_SCHEDULER.DROP_SINGLE_SCHEDULE
The prototype of the DBE_SCHEDULER.DROP_SINGLE_SCHEDULE function is as follows:
1 2 3 4
DBE_SCHEDULER.drop_single_schedule( schedule_name text, force boolean default false )
- DBE_SCHEDULER.CREATE_JOB_CLASS
Creates the class of a scheduled task.
The prototype of the DBE_SCHEDULER.CREATE_JOB_CLASS function is as follows:
1 2 3 4 5 6 7 8
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 )
- DBE_SCHEDULER.DROP_JOB_CLASS
Deletes the class of a scheduled task.
The prototype of the DBE_SCHEDULER.DROP_JOB_CLASS function is as follows:
1 2 3 4
DBE_SCHEDULER.drop_job_class( job_class_name text, force boolean default false )
- DBE_SCHEDULER.DROP_SINGLE_JOB_CLASS
Deletes the class of a single scheduled task.
The prototype of the DBE_SCHEDULER.DROP_SINGLE_JOB_CLASS function is as follows:
1 2 3 4
DBE_SCHEDULER.drop_single_job_class( job_class_name text, force boolean default false )
- DBE_SCHEDULER.GRANT_USER_AUTHORIZATION
Grants the scheduled task 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:
1 2 3 4
DBE_SCHEDULER.grant_user_authorization( username text, privilege text )
- DBE_SCHEDULER.REVOKE_USER_AUTHORIZATION
Revokes the scheduled task 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:
1 2 3 4
DBE_SCHEDULER.revoke_user_authorization( username text, privilege text )
- 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:
1 2 3 4 5 6 7 8
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 )
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:
1 2 3 4
DBE_SCHEDULER.drop_credential( credential_name text, force boolean default false )
- DBE_SCHEDULER.ENABLE
The prototype of the DBE_SCHEDULER.ENABLE function is as follows:
1 2 3 4
DBE_SCHEDULER.enable( name text, commit_semantics text default 'STOP_ON_FIRST_ERROR' )
- DBE_SCHEDULER.ENABLE_SINGLE
The prototype of the DBE_SCHEDULER.ENABLE_SINGLE function is as follows:
1 2 3
DBE_SCHEDULER.enable_single( name text )
- 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:
1 2 3 4 5
DBE_SCHEDULER.disable( name text, force boolean default false, commit_semantics text default 'STOP_ON_FIRST_ERROR' )
- DBE_SCHEDULER.DISABLE_SINGLE
The prototype of the DBE_SCHEDULER.DISABLE_SINGLE function is as follows:
1 2 3 4
DBE_SCHEDULER.disable_single( name text, force boolean default false )
- DBE_SCHEDULER.EVAL_CALENDAR_STRING
Analyzes the scheduling task period.
Return type: timestamp with time zone
The prototype of the DBE_SCHEDULER.EVAL_CALENDAR_STRING function is as follows:
1 2 3 4 5
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
- DBE_SCHEDULER.EVALUATE_CALENDAR_STRING
Analyzes the scheduling task period.
The prototype of the DBE_SCHEDULER.EVALUATE_CALENDAR_STRING function is as follows:
1 2 3 4 5 6
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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
