kingbaseV8R6使用数据库任务

金仓定时任务配置

文档：https://betheme.net/xiaochengxu/42058.html?action=onClick

https://help.kingbase.com.cn/v8/development/sql-plsql/ref-extended-plug-in/kdb_schedule.html?highlight=kdb_schedule#remove

添加kdb_schedule

shared_preload_libraries = 'kdb_schedule'

修改kdb_schedule所需参数：

#job_queue_processes如果为0，执行alter system set job_queue_processes=200；

trade=# show job_queue_processes ;

 job_queue_processes

---------------------

 0    

(1 行记录)

1. job_queue_processes 允许用户启动的最大并发数，当其值设置为0时，表示不启动自动作业功能,默认为0，不开启自动作业。
2. sys_job.log_level 用于设置JOB后台进程的日志级别，更改后需要重新加载配置文件，可选项：LOG_ERROR，LOG_WARNING，LOG_DEBUG，默认为LOG_ERROR。
3. sys_job.poll_time 用于设置轮询系统表间隔时间,单位秒，默认值为10秒。

alter system set job_queue_processes=5;

加载kdb_schedule插件

trade=# create extension kdb_schedule;

trade=# \dx kdb_schedule

                       已安装扩展列表

     名称     | 版本 | 架构模式 |            描述            

--------------+------+----------+----------------------------

 kdb_schedule | 1.0  | sys      | A KingbaseES job scheduler

(1 行记录)

trade=# \dx+ kdb_schedule

                                                    对象用于扩展 "kdb_schedule"

show shared_preload_libraries 查询

# 在shared_preload_libraries 添加 kdb_schedule。

shared_preload_libraries = 'kdb_schedule, liboracle_parser, synonym, plsql, force_view, kdb_flashback,plugin_debugger, plsql_plugin_debugger, plsql_plprofiler, ora_commands,kdb_ora_expr, sepapower, dblink, sys_kwr, sys_ksh, sys_spacequota, sys_stat_statements, backtrace, kdb_utils_function, auto_bmr,kdb_database_link'

创建program命令

begin ;

  call  dbms_scheduler.create_program(program_name        => 'prog_bzf_scheduled_task',

                                program_type        => 'PLSQL_BLOCK',        

                                program_action      => 'call pck_bzf_scheduled_task.talent_ocridentify()',

                                acconnstr           => 'user=csbzf dbname=trade port=123456 password=transcend',

                                acdbname            => 'trade',

                                number_of_arguments => 0,

                                enabled             => true,

                                comments            => '描人才资格审核ocr自动审核实现定时处理');

end;

创建scheduler

begin ;

  call dbms_scheduler.create_schedule(schedule_name   => 'schedule_01',

                                      start_date      => now(),

                                      repeat_interval => 'freq=minutely;interval=1',

                                      end_date        => null,

                                       comments        => '人才资格定时自动审核');

end;

 创建Job

begin  

 call dbms_scheduler.create_job(job_name         => 'job_01',

                            program_name     => 'prog_bzf_scheduled_task',

                            schedule_name    => 'schedule_01',

                            job_class        => 'routine maintenance',

                            enabled          => true,

                            auto_drop        => true,

                            comments         => '保障房人才资格定时任务',

                            credentail_name  => null,

                            destination_name => null);

End

调用job

call dbms_job.instance(1,'user=csbzf dbname=trade port=54321 password=transcend');

1：jobid 、连接串

启动定时任务（2种方式）

call dbms_job.broken(2,false,now());

ture：关闭

False：开启

call DBMS_SCHEDULER.ENABLE('job_01', 'STOP_ON_FIRST_ERROR');

call DBMS_SCHEDULER.DISENABLE()（关闭）

查询视图

select * from "kdb_schedule"."user_scheduler_job_run_details"

select * from "kdb_schedule"."dba_scheduler_job_log" ;

select * from "kdb_schedule"."user_scheduler_job_log" 

Select * from "kdb_schedule"."user_scheduler_job_run_details" 

查询调度

select * from kdb_schedule.kdb_schedule;

select * from kdb_job;

删除job

call DBMS_JOB.REMOVE(jobid);