Last Updated on 7 November 2024 by Roberto De Pedrini
I take inspiration from a response by Michael Mayer on the Midrange.com mailing lists to someone who asked how to monitor the messages in QSYSOPR and their corresponding replies.
With this simple SQL query, it is possible to check the messages from a specific day or a defined period, obtaining a list of the messages and their corresponding replies, along with the timestamp of both;
SELECT A.MESSAGE_TIMESTAMP AS "Date/Time of Error Msg",
A.MESSAGE_ID AS "Msg ID",
A.FROM_JOB AS "Job Generated Error Msg",
A.MESSAGE_TEXT AS "Msg Text",
B.MESSAGE_TIMESTAMP AS "Reply Date/Time",
SUBSTR(B.MESSAGE_TEXT, 1, 10) AS "Reply",
A.FROM_USER AS "Reply by"
FROM TABLE (QSYS2.MESSAGE_QUEUE_INFO(QUEUE_LIBRARY => 'QSYS',
QUEUE_NAME => 'QSYSOPR',
SEVERITY_FILTER => 99)) A,
LATERAL (SELECT MESSAGE_TIMESTAMP,
MESSAGE_TEXT, FROM_USER
FROM TABLE (QSYS2.MESSAGE_QUEUE_INFO(QUEUE_LIBRARY => 'QSYS',
QUEUE_NAME => 'QSYSOPR',
SEVERITY_FILTER => 99))
--- Single Date
--- WHERE CHAR(A.MESSAGE_TIMESTAMP) LIKE '%2024-01-15%'
--- AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B
--- Range of Dates
WHERE CHAR(A.MESSAGE_TIMESTAMP) >= '2024-09-01' AND
CHAR(A.MESSAGE_TIMESTAMP) <= '2024-09-13'
AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B
ORDER BY "Date/Time of Error Msg" ASC;We can use this example to query other conditions and messages, not just QSYSOPR!
HTH
Roberto
Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more
DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more
Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more
Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more
