DB2 Logging in stored procedure

Question

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.

Answer 1

--#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