Oct 10, 2025
Sometimes you need a method to diagnose the QSYSOPR message queue to find out when a message was answered, who answered it and how it was answered.
This SQL provides that for you. The SQL posted here looks back 7 days and is adjustable.
--- QSYSOPR System Messages ---
--- Who answered the message? ---
--- Columns prefixed with the "A" come from the error result
--- and those "B" from the reply result.
--- Who answered the message? ---
--- Columns prefixed with the "A" come from the error result
--- and those "B" from the reply result.
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
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))
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))
WHERE CHAR(A.MESSAGE_TIMESTAMP) >= CURRENT DATE - 7 DAYS AND
CHAR(A.MESSAGE_TIMESTAMP) <= CURRENT DATE
AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B
ORDER BY "Date/Time of Error Msg" ASC
CHAR(A.MESSAGE_TIMESTAMP) <= CURRENT DATE
AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B
ORDER BY "Date/Time of Error Msg" ASC
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------
Other date options are:
--- Single Date
--- WHERE CHAR(A.MESSAGE_TIMESTAMP) LIKE '%2025-08-03%'
--- AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B
--- WHERE CHAR(A.MESSAGE_TIMESTAMP) LIKE '%2025-08-03%'
--- AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B
--- Or
--- Range of Hard Coded Dates
--- WHERE CHAR(A.MESSAGE_TIMESTAMP) >= YYYY-MM-DD' AND
--- CHAR(A.MESSAGE_TIMESTAMP) <= 'YYYY-MM-DD'
--- AND ASSOCIATED_MESSAGE_KEY = A.MESSAGE_KEY) B
