为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:
【CPU】:
【问题描述】*:创建了一个存储过程,也创建了作业来执行这个存储过程,但测试发现,一直没有该作业执行记录,按理说正常执行,就算失败也有作业日志记录;有大佬帮我看下,下面是我的存储过程,以及作业调度:
CREATE OR REPLACE PROCEDURE "TV_FOREST_TEST"."getFalseRate"
AS
-- 声明变量
dataCnt INT;
falseRate FLOAT;
trueCnt INT;
noRepeatCnt INT;
repeatTrueCnt INT;
shieldCnt INT;
repeatFalseCnt INT;
totalCnt INT;
thresConfirmCnt INT;
checkCnt INT;
BEGIN
select count(a.id) into trueCnt from tb_alter a
where a.alarmTime > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and a.alarmTime < CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59');
select count(a.id) into thresConfirmCnt from tb_alter a
where a.thresConfirm = 1 and a.alarmTime > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and a.alarmTime < CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59');
select count(ah.id) into noRepeatCnt from tb_alter_temp ah
where ah.alarmTime > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and ah.alarmTime <= CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59');
select count(ar.id) into repeatTrueCnt from tb_algoRepeat ar
where ar.alarm_time > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and ar.alarm_time <= CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59') and ar.type = 1;
select count(ar.id) into shieldCnt from tb_algoRepeat ar
where ar.alarm_time > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and ar.alarm_time <= CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59') and ar.type = 2;
select count(arf.id) into repeatFalseCnt from tb_algoRepeatFalse arf
where arf.alarm_time > CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 00:00:00') and arf.alarm_time <= CONCAT(date_format(NOW(), '%Y-%m-%d'), ' 23:59:59');
totalCnt := noRepeatCnt + repeatTrueCnt + shieldCnt + repeatFalseCnt;
falseRate := (noRepeatCnt - trueCnt) / noRepeatCnt * 100;
select count(tf.id) into dataCnt from TestModel_falseRate tf where tf.date = DATE_FORMAT(NOW(),'%Y-%m-%d');
checkCnt := noRepeatCnt - thresConfirmCnt;
IF dataCnt > 0 THEN
UPDATE
TestModel_falseRate
SET
"time" = TO_CHAR(SYSDATE, 'HH24:MI:SS'),
falseRate = TO_CHAR(ROUND(falseRate, 2), '999999.99') || '%',
trueCnt = trueCnt,
thresConfirmCnt = thresConfirmCnt,
checkCnt = checkCnt,
noRepeatCnt = noRepeatCnt,
repeatTrueCnt = repeatTrueCnt,
shieldCnt = shieldCnt,
repeatFalseCnt = repeatFalseCnt,
totalCnt = totalCnt
WHERE
"date" = TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD');
ELSE
INSERT INTO TestModel_falseRate("date", "time", falseRate, trueCnt, thresConfirmCnt,
checkCnt, noRepeatCnt,repeatTrueCnt, shieldCnt, repeatFalseCnt, totalCnt)
VALUES (TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD'),
TO_CHAR(SYSDATE, 'HH24:MI:SS'),
TO_CHAR(ROUND(falseRate, 2), '999999.99') || '%',
trueCnt,
thresConfirmCnt,
checkCnt,
noRepeatCnt,
repeatTrueCnt,
shieldCnt,
repeatFalseCnt,
totalCnt);
END IF;
END;
call SP_CREATE_JOB('置信度自动确认函数',1,0,'',0,0,'',0,'统计告警表中通过置信度自动确认的告警');
call SP_JOB_CONFIG_START('置信度自动确认函数');
call SP_ADD_JOB_STEP('置信度自动确认函数', 'STP2', 0, 'call "TV_FOREST_TEST".getFalseRate();', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('置信度自动确认函数', '置信度自动确认告警统计函数', 1, 1, 1, 0, 0, '10:26:00', NULL, '2024-12-18 17:29:46', NULL, '每日23:59:00执行一次');
call SP_JOB_CONFIG_COMMIT('置信度自动确认函数');
看看能不能调高执行频率,应用侧检查下,看看它是执行了没记录,还是根本不执行。
时间配置错了吧
CALL SP_CREATE_JOB('置信度自动确认函数', 1, 0, '', 0, 0, '', 0, '统计告警表中通过置信度自动确认的告警');
CALL SP_JOB_CONFIG_START('置信度自动确认函数');
CALL SP_ADD_JOB_STEP('置信度自动确认函数', '置信度自动确认函数', 0, 'call TV_FOREST_TEST.getFalseRate();', 1, 1, 0, 0, NULL, 0);
CALL SP_ADD_JOB_SCHEDULE('置信度自动确认函数', '置信度自动确认函数', 1, 1, 1, 0, 0, '09:22:55', NULL, '2024-12-19 09:19:55', NULL, '每日23:59:00执行一次');
CALL SP_JOB_CONFIG_COMMIT('置信度自动确认函数');
到时间了,表中无数据,未看到执行日志