存储过程的日志模块设计
1.背景
在使用存储过程的时候，没有可用的日志框架。所以我们需要设计一个日志模块，用来记录系统中每一步的操作，方便后续问题的排查。
2.方案设计
存储过程的日志模块设计
1.背景
在使用存储过程的时候，没有可用的日志框架。所以我们需要设计一个日志模块，用来记录系统中每一步的操作，方便后续问题的排查。
2.方案设计
ETL_PROC_LOG: 专注于ETL（抽取、转换、加载）过程的日志，记录了系统级别的操作和数据处理情况。它包含了处理描述、起始时间、运行时间、记录行数等信息，有助于监控和优化ETL流程，并进行问题诊断。
T_PROC_LOG: 主要用于跟踪业务模块中的存储过程的执行情况。它记录了每个步骤的详细信息，如开始和结束时间、运行时间、状态和错误信息。这种细节有助于深入了解具体业务流程中的每个步骤的表现和潜在问题。
3.实现
DB2数据库建表语句
CREATE TABLE "REPORT "."T_PROC_LOG" (
"AS_OF_DATE" DATE ,
"PROC_NAME" VARCHAR(100 OCTETS) ,
"BEGIN_TIME" TIMESTAMP ,
"END_TIME" TIMESTAMP ,
"RUN_TIME" INTEGER ,
"STEP_NO" INTEGER ,
"STATUS" INTEGER ,
"ERROR_MESSAGE" VARCHAR(1024 OCTETS) )
IN "REPORT_DATA"
ORGANIZE BY ROW ;
COMMENT ON COLUMN "REPORT"."T_PROC_LOG"."AS_OF_DATE" IS '数据日期' ;
COMMENT ON COLUMN "REPORT"."T_PROC_LOG"."BEGIN_TIME" IS '开始时间' ;
COMMENT ON COLUMN "REPORT"."T_PROC_LOG"."END_TIME" IS '结束时间' ;
COMMENT ON COLUMN "REPORT"."T_PROC_LOG"."ERROR_MESSAGE" IS 'ERROR_MESSAGE' ;
COMMENT ON COLUMN "REPORT"."T_PROC_LOG"."PROC_NAME" IS '存储过程名称' ;
COMMENT ON COLUMN "REPORT"."T_PROC_LOG"."RUN_TIME" IS '运行时间' ;
COMMENT ON COLUMN "REPORT"."T_PROC_LOG"."STATUS" IS '存储过程运行状态 -1:失败; 0:成功' ;
COMMENT ON COLUMN "REPORT"."T_PROC_LOG"."STEP_NO" IS '步骤编号' ;
CREATE TABLE "REPORT"."ETL_PROC_LOG" (
"AS_OF_DATE" DATE ,
"PROCESS" VARCHAR(100 OCTETS) ,
"PROC_DESC" VARCHAR(100 OCTETS) ,
"START_TIME" TIMESTAMP ,
"END_TIME" TIMESTAMP ,
"RUN_TIME" DECIMAL(12,2) ,
"ROW_COUNT_S" DECIMAL(12,2) ,
"ROW_COUNT_R" DECIMAL(12,2) ,
"RUN_FLAG" INTEGER ,
"SQL_COD" VARCHAR(100 OCTETS) ,
"SQL_STR" VARCHAR(4000 OCTETS) )
IN "REPORT_DATA"
ORGANIZE BY ROW ;
COMMENT ON COLUMN "REPORT"."ETL_PROC_LOG"."AS_OF_DATE" IS '数据日期' ;
COMMENT ON COLUMN "REPORT"."ETL_PROC_LOG"."END_TIME" IS '结束时间' ;
COMMENT ON COLUMN "REPORT"."ETL_PROC_LOG"."PROCESS" IS '脚本代码' ;
COMMENT ON COLUMN "REPORT"."ETL_PROC_LOG"."PROC_DESC" IS '脚本描述' ;
COMMENT ON COLUMN "REPORT"."ETL_PROC_LOG"."RUN_FLAG" IS '运行状态' ;
COMMENT ON COLUMN "REPORT "."ETL_PROC_LOG"."RUN_TIME" IS '运行时间(秒)'
COMMENT ON COLUMN "REPORT "."ETL_PROC_LOG"."SQL_COD" IS '错误码' ;
COMMENT ON COLUMN "REPORT "."ETL_PROC_LOG"."SQL_STR" IS '错误信息' ;
COMMENT ON COLUMN "REPORT "."ETL_PROC_LOG"."START_TIME" IS '开始时间' ;
表T_PROC_LOG的存储过程
CREATE PROCEDURE "REPORT"."PROC_ETL_SAVELOG" (
IN "I_AS_OF_DATE" DATE,
IN "I_PROCNAME" VARCHAR(100),
IN "I_BEGINTIME" TIMESTAMP,
IN "I_STEPNO" INTEGER,
IN "I_STATUS" INTEGER,
IN "I_ERRMSG" VARCHAR(1024) )
BEGIN
DECLARE V_PROC_NAME VARCHAR (100) DEFAULT 'LOGGER.PROC_ETL_SAVELOG'; -- 本存储过程名称
DECLARE V_STATUS INTEGER DEFAULT 0; -- 状态
/* ================ 程序异常处理 ================ */
DECLARE ERROR_MESSAGE VARCHAR (1024) DEFAULT '';--
DECLARE V_ERR_MESSAGE VARCHAR (1024);--
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 ERROR_MESSAGE = MESSAGE_TEXT;--
VALUES ERROR_MESSAGE INTO V_ERR_MESSAGE;--
SET V_STATUS = -1;--
ROLLBACK WORK;--
INSERT INTO REPORT.T_PROC_LOG (
AS_OF_DATE, -- 数据日期
PROC_NAME, -- 存储过程名称
BEGIN_TIME, -- 开始时间
END_TIME, -- 结束时间
RUN_TIME, -- 执行时间
STEP_NO, -- 步骤编号,用来标识一个存储过程多个代码块
STATUS, -- 存储过程执行状态,0:成功,-1 失败
ERROR_MESSAGE -- 错误信息
)
VALUES (I_AS_OF_DATE,
V_PROC_NAME,
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
0,
1,
V_STATUS,
V_ERR_MESSAGE);--
COMMIT WORK;--
END;--
INSERT INTO REPORT.T_PROC_LOG
(
AS_OF_DATE, -- 数据日期
PROC_NAME, -- 存储过程名称
BEGIN_TIME, -- 开始时间
END_TIME, -- 结束时间
RUN_TIME, -- 执行时间
STEP_NO, -- 步骤编号,用来标识一个存储过程多个代码块
STATUS, -- 存储过程执行状态,0:成功,-1 失败
ERROR_MESSAGE -- 错误信息
)
VALUES (I_AS_OF_DATE,
I_PROCNAME,
I_BEGINTIME,
CURRENT TIMESTAMP,
CURRENT TIMESTAMP - I_BEGINTIME,
I_STEPNO,
I_STATUS,
I_ERRMSG);--
COMMIT;--
END ;
表ETL_PROC_LOG的存储过程
CREATE PROCEDURE "REPORT"."PROC_ETL_LOG" (
IN "V_AS_OF_DATE" DATE,
IN "V_PROCESS" VARCHAR(100),
IN "V_PROC_DESC" VARCHAR(100),
IN "V_ROW_COUNT_S" INTEGER,
IN "V_ROW_COUNT_R" INTEGER,
IN "V_RUNFLAG" INTEGER,
IN "V_SQL_COD" VARCHAR(100),
IN "V_SQL_STR" VARCHAR(4000) )
/*=====================================================================+
函数名称：PROC_ETL_LOG
参 数:
返 回 值：
功能描述：记录日志
目 标 表：
源 表：
编写人员：
编写日期：
修改历史：
修改日期：
修改人员：
修改原因：
-----------------------------------------------------------------------
-----------------------------------------------------------------------
+=====================================================================*/
BEGIN
/*************************开始定义变量*********/
DECLARE V_SYSDATE timestamp;--
DECLARE VV_SQL_STR VARCHAR(4000); --
/*************************结束变量定义**********/
/*初始化变量*/
select current timestamp INTO V_SYSDATE from sysibm.sysdummy1;--
CASE V_RUNFLAG
WHEN 0 THEN
--正在运行
DELETE FROM REPORT.ETL_PROC_LOG
WHERE AS_OF_DATE = V_AS_OF_DATE AND PROCESS = UPPER(TRIM(V_PROCESS));--
COMMIT;--
-- dbms_output.put_line(1||V_PROCESS);--
INSERT INTO REPORT.ETL_PROC_LOG
VALUES
(V_AS_OF_DATE, --DATA_DATE
UPPER(TRIM(V_PROCESS)),
V_PROC_DESC,
V_SYSDATE, --START_TIME
NULL, --END_TIME
NULL, --RUN_TIME
0, --ROW_COUNT_O
0, --ROW_COUNT_R
0, --RUN_FLAG
V_SQL_COD, --SQL_COD
V_SQL_STR); --SQL_STR
COMMIT;--
--SQL_STR
WHEN 1 THEN
--正确结束
UPDATE REPORT.ETL_PROC_LOG
SET PROC_DESC = V_PROC_DESC,
END_TIME = V_SYSDATE,
RUN_TIME = NVL(timestampdiff(2,char(V_SYSDATE - START_TIME)),0),
ROW_COUNT_S = V_ROW_COUNT_S,
ROW_COUNT_R = V_ROW_COUNT_R,
RUN_FLAG = 1,
SQL_COD = V_SQL_COD,
SQL_STR = V_SQL_STR
WHERE AS_OF_DATE = V_AS_OF_DATE
AND PROCESS = UPPER(TRIM(V_PROCESS))
AND RUN_FLAG = 0;--
COMMIT;--
-- dbms_output.put_line(3||V_PROCESS);--
WHEN -1 THEN
--运行错误
IF V_SQL_COD = 'Z001' THEN
SET VV_SQL_STR = '目标表存在和当前工作日期一致的数据,如果需要再次运行需要恢复上一日运行的数据备份';--
ELSE
SET VV_SQL_STR = V_SQL_STR;--
END IF;--
UPDATE REPORT.ETL_PROC_LOG
SET PROC_DESC = V_PROC_DESC,
END_TIME = V_SYSDATE,
--run_time=NVL((v_sysdate-v_start_time)*24*60*60,0),
RUN_TIME = NVL(timestampdiff(2,char(V_SYSDATE - START_TIME)), 0),
ROW_COUNT_S = V_ROW_COUNT_S,
ROW_COUNT_R = V_ROW_COUNT_R,
RUN_FLAG = -1,
SQL_COD = V_SQL_COD,
SQL_STR = VV_SQL_STR
WHERE AS_OF_DATE = V_AS_OF_DATE
AND PROCESS = UPPER(TRIM(V_PROCESS))
AND RUN_FLAG = 0;--
COMMIT;--
END CASE;--
END ;
调用示例
CREATE OR REPLACE PROCEDURE "REPORT"."TEST" (
IN "P_AS_OF_DATE" VARCHAR(10),
OUT "RET_FLG" INTEGER,
OUT "RET_MSG" VARCHAR(1024) )
SPECIFIC "SQL210910118414221"
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
BEGIN
/* ================ 程序变量 ================ */
DECLARE STAT_DATE DATE;
DECLARE V_DATA_DATE DATE;
DECLARE V_PROC_NAME VARCHAR(100) DEFAULT 'REPORT.TEST';
DECLARE V_BEGIN_TIME TIMESTAMP;
DECLARE V_STEPNO INTEGER DEFAULT 0;
DECLARE V_STATUS INTEGER DEFAULT 0;
DECLARE V_ERR_MESSAGE VARCHAR(1024) DEFAULT '';
/*==========程序异常处理=========*/
DECLARE ERROR_MESSAGE VARCHAR(1024) DEFAULT '';
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS EXCEPTION 1 ERROR_MESSAGE=MESSAGE_TEXT;
VALUES (ERROR_MESSAGE) INTO V_ERR_MESSAGE;
SET V_STATUS=1;
ROLLBACK WORK;
CALL REPORT.PROC_ETL_SAVELOG(TO_DATE(P_AS_OF_DATE,'YYYY-MM-DD'),V_PROC_NAME,CURRENT TIMESTAMP,V_STEPNO,V_STATUS,V_ERR_MESSAGE);
COMMIT WORK;
SET RET_FLG=1;
SET RET_MSG=V_ERR_MESSAGE;
END;
/*==========程序异常处理结束=========*/
SET RET_FLG =0;
SET RET_MSG='Execute Procedure:'||V_PROC_NAME||'Successful';
SET V_BEGIN_TIME = CURRENT_TIMESTAMP;
SET V_STEPNO = 1;
SET V_DATA_DATE=TO_DATE(P_AS_OF_DATE,'YYYY-MM-DD');
--更新分区信息
CALL REPORT.PROC_ADD_TABLE_PARTITION(V_DATA_DATE,'REPORT', 'TEST');
COMMIT WORK;
--写入初始日志
CALL REPORT.PROC_ETL_SAVELOG (TO_DATE (P_AS_OF_DATE,'YYYY-MM-DD'),V_PROC_NAME,V_BEGIN_TIME,V_STEPNO, V_STATUS,V_ERR_MESSAGE);
COMMIT WORK;
SET V_STEPNO =2;
SET V_BEGIN_TIME = CURRENT_TIMESTAMP;
--执行业务逻辑
COMMIT;
--写入初始日志
CALL REPORT.PROC_ETL_SAVELOG (TO_DATE (P_AS_OF_DATE,'YYYY-MM-DD'),V_PROC_NAME,V_BEGIN_TIME,V_STEPNO, V_STATUS,V_ERR_MESSAGE);
COMMIT WORK;
END
CREATE OR REPLACE PROCEDURE "REPORT"."TEST" (
IN "P_AS_OF_DATE" DATE,
IN "P_SCHEMA" VARCHAR(20),
IN "P_TABLE_NAME" VARCHAR(80) )
SPECIFIC SQL220830152116953
BEGIN
/*************************开始定义变量*********/
DECLARE SQLERRM VARCHAR(2000); --SQL错误信息
/*************************结束变量定义**********/
--异常处理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1 SQLERRM = MESSAGE_TEXT; -- 取得错误信息
ROLLBACK;-- 回滚
CALL REPORT.PROC_ETL_LOG(P_AS_OF_DATE, 'PROC_ADD_TABLE_PARTITION-'||UPPER(P_TABLE_NAME), UPPER(P_TABLE_NAME)||'添加分区', 0, 0, -1, 1, SQLERRM); -- 记录日志
END;--
--开始日志
CALL REPORT.PROC_ETL_LOG(P_AS_OF_DATE, 'PROC_ADD_TABLE_PARTITION-'||UPPER(P_TABLE_NAME), UPPER(P_TABLE_NAME)||'添加分区', 0, 0, 0, '', ''); -- 记录日志
--执行功能代码
--结束日志
CALL REPORT.PROC_ETL_LOG(P_AS_OF_DATE, 'PROC_ADD_TABLE_PARTITION-'||UPPER(P_TABLE_NAME), UPPER(P_TABLE_NAME)||'添加分区', 0, 0, 1, '0', '执行成功'); -- 记录日志
END
4.总结
在系统模块中，记录系统功能代码的执行情况至关重要。如果遇到异常，应捕获并记录错误信息和错误码，以便于后续的故障排查。对于业务模块，则应详细记录每个业务步骤的执行情况，并在出现异常时抛出异常。这种方法可以确保系统功能和业务逻辑的透明性，同时提供足够的信息以支持问题解决和系统维护。
1445
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
