达梦数据库作业相关SQL

--查看数据库已启用的所有作业
SELECT * FROM SYSJOB.SYSJOBS WHERE ENABLE=1;

--注意，批量启用和禁用数据库作业的SQL在不开启数据库作业执行情况通知的情况下才可使用
--批量禁用达梦数据库所有作业
SELECT 'CALL SP_ALTER_JOB('''||SJ.NAME||''',0,'||SJ.ENABLE_EMAIL||','''','||SJ.EMAIL_TYPE||','||SJ.ENABLE_NETSEND||','''','||SJ.NETSEND_TYPE||','''||SJ.DESCRIBE||''');'FROM SYSJOB.SYSJOBS SJ WHERE SJ.ENABLE=1;

--批量启用达梦数据库所有作业
SELECT 'CALL SP_ALTER_JOB('''||SJ.NAME||''',1,'||SJ.ENABLE_EMAIL||','''','||SJ.EMAIL_TYPE||','||SJ.ENABLE_NETSEND||','''','||SJ.NETSEND_TYPE||','''||SJ.DESCRIBE||''');'FROM SYSJOB.SYSJOBS SJ WHERE SJ.ENABLE=0;

查看达梦数据库备份作业信息SQL1

SELECT
SJ."NAME" "作业名称" ,
(CASE SJS."TYPE" WHEN 0 THEN '执行SQL语句' WHEN 1 THEN '数据库备份' WHEN 5 THEN '数据库备份' WHEN 6 THEN '数据库备份' ELSE '其他步骤' END)"步骤类型" ,
SJ."DESCRIBE" "作业描述信息" ,
(CASE SJ."ENABLE" WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE 'NULL' END)"作业是否启用",
(CASE SJC."TYPE" WHEN 0 THEN '只执行一次' WHEN '1' THEN '按天的频率执行' WHEN '2' THEN '按周的频率执行' ELSE '其他类型' END)"调度类型" ,
IF(SJC."TYPE"=2,(CASE SJC."FREQ_SUB_INTERVAL" WHEN 64 THEN '周六执行' WHEN 63 THEN '周日，周一至周五执行' WHEN 1 THEN '周日执行' WHEN 126 THEN '周一至周六执行' WHEN 32 THEN '周五执行' WHEN 95 THEN '周一至周四，周六、周日执行' ELSE 'NULL' END),IF(SJC."TYPE"=1,(CASE SJC."FREQ_INTERVAL" WHEN SJC."FREQ_INTERVAL" THEN '每'||SJC."FREQ_INTERVAL"||'天执行' ELSE 'NULL' END),'NULL'))"执行频率",
(CASE SJC."FREQ_MINUTE_INTERVAL" WHEN 0 THEN '只执行一次' ELSE 'NULL' END)"调度执行间隔(分钟)",
SJC."STARTTIME" "调度起始时间" ,
SJC."DURING_START_DATE" "调度起始日期" ,
SJS."DBNAME" "所属数据库",
(CASE SJC."ENABLE" WHEN 1 THEN '是' WHEN 0 THEN '否' ELSE 'NULL' END)"调度是否启用"
FROM
"SYSJOB"."SYSJOBSCHEDULES" SJC,
"SYSJOB"."SYSJOBS" SJ ,
"SYSJOB"."SYSJOBSTEPS" SJS
WHERE
SJC."VALID" ='Y'
AND SJ."VALID" ='Y'
AND SJS."JOBID" =SJ."ID"
AND SJC."JOBID" =SJ."ID"
ORDER BY SJ."ENABLE" DESC,SJC."ENABLE" DESC;

查看达梦数据库备份作业信息SQL2

   select job.id, 
          job.NAME job_name,
          STEP.NAME STEP_NAME,
          sch.name sch_name,
          CASE STEP.TYPE WHEN 0 then case when upper(step.COMMAND) like '%SF_BAKSET_REMOVE_BATCH%' THEN '备份清理' else 'SQL脚本或语句块' end WHEN 6 THEN ( CASE SUBSTR(step.COMMAND,0,1) WHEN 0 THEN '完全备份' when 1 then '差异增量备份' when 3 then '归档备份' when 4 then '累计增量备份' END) ||(case SUBSTR(step.COMMAND,2,1) when 0 then '_备份过程中的归档Y' when 1 then '_备份过程中的归档N' END) ||'_并行度' || (case SUBSTR(step.COMMAND,3,1) when 0 then 0 when 1 then 4 else SUBSTR(step.COMMAND,3,1) END) END job_type,
          case job."ENABLE" when 1 then 'Y&' else 'N&' end || case job.valid when 'Y' then 'Y' when 'N' then 'N' end "作业(启用&完整)" ,
          case sch."ENABLE" when 1 then 'Y&' else 'N&' end || case sch.valid when 'Y' then 'Y' when 'N' then 'N' else 'N' end "调度(启用&完整)" ,
          --type,
          (case sch.type when 0 then '只执行一次' when 1 then (case when sch.FREQ_INTERVAL>1 and sch.FREQ_INTERVAL<=100 then '每'||sch.FREQ_INTERVAL||'天执行' when sch.FREQ_INTERVAL=1 then '每天执行' end) when 2 then '每'||sch.FREQ_INTERVAL||'周执行' when 3 then '每'||sch.FREQ_INTERVAL||'月的第'||sch.FREQ_SUB_INTERVAL||'天执行' when 4 then '每'||sch.FREQ_INTERVAL||'月的第1周的第'||sch.FREQ_SUB_INTERVAL||'天执行' when 5 then '每'||sch.FREQ_INTERVAL||'月的第2周的第'||sch.FREQ_SUB_INTERVAL||'天执行' when 6 then '每'||sch.FREQ_INTERVAL||'月的第3周的第'||sch.FREQ_SUB_INTERVAL||'天执行' when 7 then '每'||sch.FREQ_INTERVAL||'月的第4周的第'||sch.FREQ_SUB_INTERVAL||'天执行' when 8 then '每'||sch.FREQ_INTERVAL||'月的最后一周的第'||sch.FREQ_SUB_INTERVAL||'天执行' end) ||',执行时间为：'|| (case when DM_BIT_TEST(cast(sch.FREQ_SUB_INTERVAL as BINARY),2)=1 then '周一' end) || (case when DM_BIT_TEST(cast(sch.FREQ_SUB_INTERVAL as BINARY),3)=1 then '周二' end) || (case when DM_BIT_TEST(cast(sch.FREQ_SUB_INTERVAL as BINARY),4)=1 then '周三' end) || (case when DM_BIT_TEST(cast(sch.FREQ_SUB_INTERVAL as BINARY),5)=1 then '周四' end) || (case when DM_BIT_TEST(cast(sch.FREQ_SUB_INTERVAL as BINARY),6)=1 then '周五' end) || (case when DM_BIT_TEST(cast(sch.FREQ_SUB_INTERVAL as BINARY),7)=1 then '周六' end) || (case when DM_BIT_TEST(cast(sch.FREQ_SUB_INTERVAL as BINARY),1)=1 then '周日' end) || sch.STARTTIME|| case when sch.ENDTIME is not null and trim(sch.ENDTIME)<>'' then '-'||sch.ENDTIME||'; ' end || case when sch.FREQ_MINUTE_INTERVAL>0 then '每隔'|| case when sch.FREQ_MINUTE_INTERVAL<=1439 then sch.FREQ_MINUTE_INTERVAL ||'分钟' when sch.FREQ_MINUTE_INTERVAL>32768 then (sch.FREQ_MINUTE_INTERVAL-32768)||'秒' end || '执行一次' end|| case when sch.DURING_END_DATE is not null and trim(sch.DURING_END_DATE)<>'' then ', 持续时间为：'|| sch.DURING_START_DATE||'-'||sch.DURING_END_DATE else ', 开始时间为：'||sch.DURING_START_DATE||', 无结束时间' end sch_rate,
          job.USERNAME creator,
          job.DESCRIBE,
          case substr(STEP.COMMAND,2,1) when 0 then '不压缩,BAK_PATH:'|| SUBSTR(step.COMMAND,9) when 'F' then '非备份,command：'||step.COMMAND else (case STEP.TYPE when 0 then '非备份,command：'||step.COMMAND else (case when substr(STEP.COMMAND,2,1) is null then 'ERROR' else substr(STEP.COMMAND,2,1)||'级压缩,BAK_PATH:'|| SUBSTR(step.COMMAND,9) end) END ) end BAKINFO,
          (case job.sys_flag when 0 then (SELECT case when his.ERRCODE=0 then his.end_time||':success,耗时'||datediff(ss,his.start_time,his.end_time)||'s' else his.ERRCODE||':'||his.end_time||'_'||his.ERRINFO end
                                  FROM sysjob.SYSJOBHISTORIES2 his
                                 WHERE job.NAME=HIS.name
                              ORDER BY HIS.START_TIME DESC LIMIT 1) when 2 then (SELECT case when his.ERRCODE=0 then his.end_time||':success,耗时'||datediff(ss,his.start_time,his.end_time)||'s' else his.ERRCODE||':'||his.end_time||'_'||his.ERRINFO end
                                  FROM sysjob.SYSJOBHISTORIES2 his
                                 WHERE job.NAME=HIS.name
                              ORDER BY HIS.START_TIME DESC LIMIT 1) end) LAST_EXEC_RESULT
     from sysjob.SYSJOBS job
left join sysjob.SYSJOBSCHEDULES sch
       on sch.JOBID=job.id
left JOIN SYSJOB.SYSJOBSTEPS STEP
       ON STEP.JOBID=JOB.ID
    where sch.valid is not null
union all
   select job.id, 
          job.NAME job_name,
          STEP.NAME STEP_NAME,
          sch.name sch_name,
          CASE STEP.TYPE WHEN 0 then case when upper(step.COMMAND) like '%SF_BAKSET_REMOVE_BATCH%' THEN '备份清理' else 'SQL脚本或语句块' end WHEN 6 THEN ( CASE SUBSTR(step.COMMAND,0,1) WHEN 0 THEN '完全备份' when 1 then '差异增量备份' when 3 then '归档备份' when 4 then '累计增量备份' END) ||(case SUBSTR(step.COMMAND,2,1) when 0 then '&备份过程中的归档Y' when 1 then '&备份过程中的归档N' END) ||'&并行度' || (case SUBSTR(step.COMMAND,3,1) when 0 then 0 when 1 then 4 else SUBSTR(step.COMMAND,3,1) END) END job_type,
          case job."ENABLE" when 1 then 'Y&' else 'N&' end || case job.valid when 'Y' then 'Y' when 'N' then 'N' end "作业(启用&完整)" ,
          case sch."ENABLE" when 1 then 'Y&' else 'N&' end || case sch.valid when 'Y' then 'Y' when 'N' then 'N' else 'N' end "调度(启用&完整)" ,
          --type,
          'ERROR' sch_rate,
          job.USERNAME creator,
          job.DESCRIBE,
          case substr(STEP.COMMAND,2,1) when 0 then '不压缩,BAK_PATH:'|| SUBSTR(step.COMMAND,9) when 'F' then '非备份,command：'||step.COMMAND else (case STEP.TYPE when 0 then '非备份,command：'||step.COMMAND else (case when substr(STEP.COMMAND,2,1) is null then 'ERROR' else substr(STEP.COMMAND,2,1)||'级压缩,BAK_PATH:'|| SUBSTR(step.COMMAND,9) end) END ) end BAKINFO,
          (case job.sys_flag when 0 then (SELECT case when his.ERRCODE=0 then his.end_time||':success,耗时'||datediff(ss,his.start_time,his.end_time)||'s' else his.ERRCODE||':'||his.end_time||'_'||his.ERRINFO end
                                  FROM sysjob.SYSJOBHISTORIES2 his
                                 WHERE job.NAME=HIS.name
                              ORDER BY HIS.START_TIME DESC LIMIT 1) when 2 then (SELECT case when his.ERRCODE=0 then his.end_time||':success,耗时'||datediff(ss,his.start_time,his.end_time)||'s' else his.ERRCODE||':'||his.end_time||'_'||his.ERRINFO end
                                  FROM sysjob.SYSJOBHISTORIES2 his
                                 WHERE job.NAME=HIS.name
                              ORDER BY HIS.START_TIME DESC LIMIT 1) end) LAST_EXEC_RESULT
     from sysjob.SYSJOBS job
left join sysjob.SYSJOBSCHEDULES sch
       on sch.JOBID=job.id
left JOIN SYSJOB.SYSJOBSTEPS STEP
       ON STEP.JOBID=JOB.ID
    where sch.valid is null;

数据库备份作业创建

--创建代理环境
SP_INIT_JOB_SYS(1);
--每周六凌晨2点数据库全量备份
call SP_CREATE_JOB('DB_FULL_BAK',1,0,'',0,0,'',0,'数据库全备');
call SP_JOB_CONFIG_START('DB_FULL_BAK');
call SP_ADD_JOB_STEP('DB_FULL_BAK', 'FULL_BAK', 6, '01040000/dmbak/', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('DB_FULL_BAK', 'SCH_OF_FULL_BAK', 1, 2, 1, 64, 0, '02:00:00', NULL, '2022-11-11 13:29:11', NULL, '');
call SP_JOB_CONFIG_COMMIT('DB_FULL_BAK');
--每周日到周五凌晨2点数据库差异增量备份
call SP_CREATE_JOB('DB_INCR_BAK',1,0,'',0,0,'',0,'数据库增备');
call SP_JOB_CONFIG_START('DB_INCR_BAK');
call SP_ADD_JOB_STEP('DB_INCR_BAK', 'INCR_BAK', 6, '11040000/dmbak/|/dmbak/', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('DB_INCR_BAK', 'SCH_OF_INCR_BAK', 1, 2, 1, 63, 0, '02:00:00', NULL, '2022-11-11 13:30:54', NULL, '');
call SP_JOB_CONFIG_COMMIT('DB_INCR_BAK');
--每周六凌晨1点删除7天前备份
call SP_CREATE_JOB('DEL_BAK',1,0,'',0,0,'',0,'清理7天前的备份');
call SP_JOB_CONFIG_START('DEL_BAK');
call SP_ADD_JOB_STEP('DEL_BAK', 'del_bak', 0, 'CALL SF_BAKSET_BACKUP_DIR_ADD(''DISK'',''/dmbak'');
CALL SF_BAKSET_REMOVE_BATCH (''DISK'', sysdate-7, NULL, NULL);', 0, 0, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('DEL_BAK', 'del_bak', 1, 2, 1, 64, 0, '01:00:00', NULL, '2022-11-11 13:30:54', NULL, '');
call SP_JOB_CONFIG_COMMIT('DEL_BAK');