前言

最近有用到达梦的代理系统来配置定时作业，发现查看作业运行情况得用管理工具点点点，那么有没有好的办法通过sql直接把我需要的信息获取出来。
注：只适用于每日执行一次的作业，其他的情况我还没有考虑
需要使用到下面四表

SQL分享

查看作业信息

select a.id,a.name,a.enable,a.username,a.createtime,a.modifytime,b.name,b.enable,b.starttime,b.during_end_date,b.during_start_date,b.valid from sysjob.sysjobs a join sysjob.sysjobschedules b on a.id=b.jobid where a.enable=1;

查看作业的最近十次执行日志

select id,name,enable,createtime,modifytime,errinfo,start_time,end_time，use_time  from (
select a.id,a.name,a.enable,a.createtime,a.modifytime,b.errinfo,b.start_time,b.end_time,datediff(ss,b.start_time,b.end_time) use_time ,row_number() over( partition by b.name order by start_time desc ) as last_start from sysjob.sysjobs a join sysjob.SYSJOBHISTORIES2 b on a.name=b.name
) c where c.last_start <11  --最近10次
;

作业步骤的执行日志

select id,name,stepname,enable,createtime,modifytime,errinfo,start_time,end_time,use_time from (
select a.id,a.name,b.stepname,a.enable,a.createtime,a.modifytime,b.errinfo,b.start_time,b.end_time,datediff(ss,b.start_time,b.end_time) use_time ,row_number() over( partition by a.name,stepname order by start_time desc ) as last_start from sysjob.sysjobs a join sysjob.SYSstepHISTORIES2 b on a.name=b.name
) c where c.last_start <11
;