KingbaseES 数据库提供了 kdb_schedule 扩展，使得用户能通过类似oracle job 的方式进行job调用。kdb_schedule 提供了三个Schema ：dbms_job and dbms_scheduler 分别类似于 oracle 的 dbms_job 和 dbms_schedule 包，数据字典信息放于kdb_schedule 模式下。
一、通过dbms_job管理Job
1、创建Job
create table d_test(tid varchar2(64), insdate date); create or replace procedure p_test() as
begin
insert into d_test values(to_char(sysdate, 'yyyymmddhh24miss'), sysdate);
commit;
end;
/ DECLARE
v_jobid NUMBER;
BEGIN
dbms_job.submit(v_jobid, 'call p_test()', now(), 'Freq=Minutely;Interval=1');
COMMIT;
END;
/
2、查询Job
test=# select jobid,jobname,jobenabled,joblastrun,jobnextrun,jobrepeattimes from kdb_job;
jobid | jobname | jobenabled | joblastrun | jobnextrun | jobrepeattimes
-------+---------------------------------------------+------------+------------+-------------------------------+----------------
1 | internal_job1:2021-06-22 08:11:39.797681+08 | f | | 2021-06-22 08:11:39.797681+08 | 0
可以看到，Job 创建后，默认的状态是 jobenabled=false，也就是实际没有启用。
Note：早期版本 kdb_job 表是schema kdb_schedule下，后面移到pg_catalog schema下。
3、启用Job
job 创建后，默认是 broken(disabled) ，需要enable
test=# call dbms_job.broken(1,false);
CALL
test=# select jobid,jobname,jobenabled,joblastrun,jobnextrun,jobrepeattimes from kdb_job;
jobid | jobname | jobenabled | joblastrun | jobnextrun | jobrepeattimes
-------+---------------------------------------------+------------+------------+-------------------------------+----------------
1 | internal_job1:2021-06-22 08:11:39.797681+08 | t | | 2021-06-22 08:11:39.797681+08 | 0
enable job 后，等待一会再查，发现实际job 还是没有运行。这是为什么？这是因为kdb_schedule 后台进程没有启动。启动后台进程后，再查，可以看到job已经运行了。
test=# select jobid,jobname,jobenabled,joblastrun,jobnextrun,jobrepeattimes from kdb_job;
jobid | jobname | jobenabled | joblastrun | jobnextrun | jobrepeattimes
-------+---------------------------------------------+------------+-------------------------------+------------------------+----------------
1 | internal_job1:2021-06-22 08:11:39.797681+08 | t | 2021-06-22 08:22:39.062009+08 | 2021-06-22 08:23:39+08 | 0
(1 row)
4、Job实际上也是Schedule
test=# select * from kdb_schedule.kdb_schedule;
scid | scname | scdesc | scenabled | scstart | scend | screpeat_interval
------+--------+--------+-----------+----------------------------+-------+--------------------------
2 | | | t | 2021-06-22 10:11:25.586148 | | Freq=Minutely;Interval=1
(1 row) test=# select * from kdb_schedule.kdb_schedule_job;
sjid | sjscid | sjjobid | sjstatus | sjlasttime | sjnexttime
------+--------+---------+----------+------------+-------------------------------
2 | 2 | 1 | s | | 2021-06-22 10:11:25.586148+08
可以看到，通过dbms_job创建的job信息，同样会在kdb_schedule 和kdb_schedule_job 显示。
5、其他Job维护操作
--Job删除
test=# call dbms_job.remove(1);
CALL
二、启动kdb_schedule 进程
使用过Oracle的人都知道，Oracle数据库启动时，会运行多个ora_j000_xxx 进程，这些进程实际上就是用于运行Job 的。同样KINGBASE 也需要后台进程来运行job
[kingbase@dbhost03 ~]$ kdb_schedule "dbname=template1 user=system"
Note：kdb_schedule只需要运行一个，后面跟的数据库连接信息可以是任何一个数据库，不影响其他库job的使用。
三、通过dbms_scheduler管理Job
1、创建program
begin
dbms_scheduler.create_program(program_name => 'prog_01',
program_type => 'PLSQL_BLOCK',
program_action => 'call public.p_test()',
acdbname => 'test',
number_of_arguments => 0,
enabled => true,
comments => 'test program');
end;
这里有两个地方必须注意。'PLSQL_BLOCK' 必须大写，acdbname 必须指定。
acdbname 指明了action 所在的数据库，如果没有指定，默认指 kdb_schedule 运行时指定的数据库。成功创建program后，会有如下一行信息：
test=# select * from kdb_schedule.kdb_action;
acid | acname | acdesc | acenabled | ackind | accode | acconnstr | acdbname | acnextrun
------+---------+--------------+-----------+--------+----------------------+-----------+----------+-----------
7 | prog_01 | test program | t | s | call public.p_test() | | test |
(1 row)
2、创建Schedule
begin
dbms_scheduler.create_schedule(schedule_name => 'schedule_01',
start_date => now(),
repeat_interval => 'freq=minutely;interval=1',
end_date => null,
comments => 'test schedule');
end;
创建schedule 后，会有如下一行信息：
test=# select * from kdb_schedule.kdb_schedule;
scid | scname | scdesc | scenabled | scstart | scend | screpeat_interval
------+-------------+---------------+-----------+----------------------------+-------+--------------------------
7 | schedule_01 | test schedule | t | 2021-06-22 14:28:38.536823 | | freq=minutely;interval=1
(1 row)
3、创建Job
begin
dbms_scheduler.create_job(job_name => 'job_01',
program_name => 'prog_01',
schedule_name => 'schedule_01',
job_class => 'routine maintenance',
enabled => true,
auto_drop => true,
comments => 'test job',
credentail_name => null,
destination_name => null);
end;
创建后，下表会有相关记录信息，分别记录 job 与 schedule ， 以及 job 与 action 之间的关系。
test=# select * from kdb_schedule.kdb_schedule_job;
sjid | sjscid | sjjobid | sjstatus | sjlasttime | sjnexttime
------+--------+---------+----------+------------+------------
7 | 7 | 1 | s | | test=# select * from kdb_schedule.kdb_job_action;
jaid | jajobid | jaacid | jastatus | jalasttime
------+---------+--------+----------+------------
7 | 1 | 7 | s |
具体的job信息，可以看 kdb_job:
test=# select jobid,jobname,jobenabled,joblastrun,jobnextrun,jobrepeattimes from kdb_job;
jobid | jobname | jobenabled | joblastrun | jobnextrun | jobrepeattimes
-------+---------+------------+-------------------------------+------------------------+----------------
1 | job_01 | t | 2021-06-22 14:59:41.997986+08 | 2021-06-22 15:00:38+08 | 0
(1 row)
4、Enable and Disable Job
Job 创建时，可以指定enable or disable ，后续也可以手动修改。
begin
dbms_scheduler.disable(name => 'job_01');
end;
/ test=# select jobid,jobname,jobenabled,joblastrun,jobnextrun,jobrepeattimes from kdb_job;
jobid | jobname | jobenabled | joblastrun | jobnextrun | jobrepeattimes
-------+---------+------------+-------------------------------+------------------------+----------------
1 | job_01 | f | 2021-06-22 15:06:42.303189+08 | 2021-06-22 15:07:38+08 | 0
(1 row) begin
dbms_scheduler.enable(name => 'job_01');
end;
/ test=# select jobid,jobname,jobenabled,joblastrun,jobnextrun,jobrepeattimes from kdb_job;
jobid | jobname | jobenabled | joblastrun | jobnextrun | jobrepeattimes
-------+---------+------------+-------------------------------+------------------------+----------------
1 | job_01 | t | 2021-06-22 15:08:07.365179+08 | 2021-06-22 15:08:38+08 | 0
(1 row)
5、其他操作
begin
dbms_scheduler.drop_job(job_name => 'job_01');
end;
/ begin
dbms_scheduler.drop_schedule(schedule_name => 'schedule_01');
end;
/ begin
dbms_scheduler.drop_program(program_name => 'prog_01');
end;
/ begin
dbms_scheduler.run_job(job_name => 'job_01');
end;
/
四、故障排查
kdb_schedule.kdb_jobsteplog 记录了job 调用的具体信息，如果有错误的，jsloutput 会显示具体的错误信息。
