Diagnostic QSYSOPR Message Queue SQL

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. 

------------------------------------------------------------------------------------------------------------------------ 
------------------------------------------------------------------------------------------------------------------------       

 

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

                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 

 

------------------------------------------------------------------------------------------------------------------------ 

------------------------------------------------------------------------------------------------------------------------

 

Other date options are:

 

---  Single Date             
---                 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