英文:

DB2 Logging in stored procedure

问题

在存储过程中创建特定模式（如调试或信息）的日志输出是否可能？
我只知道DBMS_OUTPUT.PUT_LINE命令，但我需要指定日志级别。

英文:

Is it possible to create log outputs in a specific mode like debug or info within a stored procedure?
I just know only awared about the cmd DBMS_OUTPUT.PUT_LINE. But I need it with specification of the log level.

答案1

得分: 0

--#设置终止符@
CREATE TABLE LOG (TS TIMESTAMP NOT NULL, MSG VARCHAR (100))@

CREATE OR REPLACE PROCEDURE LOGGER (P_MSG VARCHAR (100))
AUTONOMOUS 
BEGIN
	INSERT INTO LOG (TS, MSG) VALUES (GENERATE_UNIQUE()::TIMESTAMP, P_MSG);
END@

CREATE TABLE TEST (I INT)@

CREATE OR REPLACE TRIGGER TEST_AIR
AFTER INSERT ON TEST
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
  CALL LOGGER ('Start of insertion: ' || N.I);
  CALL DBMS_ALERT.SLEEP (3);
  CALL LOGGER ('End of inserion: ' || N.I);
END@

INSERT INTO TEST VALUES 1, 2@
SELECT * FROM LOG ORDER BY TS@

TS	MSG
2023-02-13-17.41.57.098209	Start of insertion: 1
2023-02-13-17.42.00.115693	End of inserion: 1
2023-02-13-17.42.00.137199	Start of insertion: 2
2023-02-13-17.42.03.163761	End of inserion: 2

<details>
<summary>英文:</summary>

--#SET TERMINATOR @
CREATE TABLE LOG (TS TIMESTAMP NOT NULL, MSG VARCHAR (100))@

CREATE OR REPLACE PROCEDURE LOGGER (P_MSG VARCHAR (100))
AUTONOMOUS
BEGIN
INSERT INTO LOG (TS, MSG) VALUES (GENERATE_UNIQUE()::TIMESTAMP, P_MSG);
END@

CREATE TABLE TEST (I INT)@

CREATE OR REPLACE TRIGGER TEST_AIR
AFTER INSERT ON TEST
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
CALL LOGGER ('Start of insertion: ' || N.I);
CALL DBMS_ALERT.SLEEP (3);
CALL LOGGER ('End of inserion: ' || N.I);
END@

INSERT INTO TEST VALUES 1, 2@
SELECT * FROM LOG ORDER BY TS@

|TS|MSG|
|--|---|
|2023-02-13-17.41.57.098209|Start of insertion: 1|
|2023-02-13-17.42.00.115693|End of inserion: 1|
|2023-02-13-17.42.00.137199|Start of insertion: 2|
|2023-02-13-17.42.03.163761|End of inserion: 2|


</details>