SQL queries for messaging engine state assessment

View the state of the messaging engine directly from the database when troubleshooting potential object queue issues.

You can use the following SQL queries to assess the state of the messaging engine.

The following query shows the runtime configuration of object queues.

SELECT 
         A.ID, A.CLASSCODE, A.NAME, A.CAPTION, A.PRIORITY, A.CONCURRENCY, A.CUSTOMIZED, A.SYSTEM, A.THROTTLECOUNTERID,
         TIMEOUT, SENDENABLED, RECEIVEENABLED, ERRORCOUNT, C.NAME ERRORQUEUE, NODETYPE
FROM 
         OQQUEUE A 
         JOIN OQOBJECTQUEUE B ON B.ID = A.ID
         LEFT JOIN OQQUEUE C ON C.ID = B.ERRORQUEUEID
ORDER BY
         A.NAME

The following query shows the messages that are not in the error queue.

SELECT 
        A.*, B.NAME QUEUENAME, C.NAME ORIGINALQUEUE, D.NAME RESENDQUEUE 
FROM 
        OQMESSAGE A
        JOIN OQQUEUE B ON B.ID = A.QUEUEID
        JOIN OQQUEUE C ON C.ID = A.ORIGINALQUEUEID
        JOIN OQQUEUE D ON D.ID = A.RESENDQUEUEID
WHERE 
        B.ID NOT IN (SELECT DISTINCT (ERRORQUEUEID) FROM OQOBJECTQUEUE WHERE 
        ERRORQUEUEID IS NOT NULL)
        /*/*UNCOMMENT TO FILTER BY A STATE*/ AND A.STATECODE = /*/*blocked*/'B'*/  
        /*/*dispatched*/'D'*/  /*/*errored*/'E'*/ /*/*new*/'N'*/  /*/*currently 
        processing*/'P'*/  /*/*waiting on a resource*/'W'*/ */
ORDER BY 
        A.NAME, A.DELIVERYTIME ASC

The following query shows the messages that are in the error queue.

SELECT 
         A.*, B.NAME ERRORQUEUENAME 
FROM 
         OQMESSAGE A
         JOIN OQQUEUE B ON B.ID = A.QUEUEID
WHERE 
         A.QUEUEID IN (SELECT ERRORQUEUEID FROM OQOBJECTQUEUE)
ORDER BY 
         A.DELIVERYTIME ASC

The following query shows the messages that are ready for processing but are waiting for a node.

SELECT 
         A.*, B.NAME QUEUENAME, C.NAME ORIGINALQUEUE, D.NAME RESENDQUEUE 
FROM 
         OQMESSAGE A
         JOIN OQQUEUE B ON B.ID = A.QUEUEID  
         JOIN OQQUEUE C ON C.ID = A.ORIGINALQUEUEID
         JOIN OQQUEUE D ON D.ID = A.RESENDQUEUEID
         AND A.STATECODE = 'P' AND NODEID = 0X0
ORDER BY 
         A.NAME, A.DELIVERYTIME ASC

The following query shows the currently processing messages by node.

SELECT 
        E.NAME NODENAME, A.*, B.NAME QUEUENAME, C.NAME ORIGINALQUEUE, D.NAME RESENDQUEUE 
FROM 
        OQMESSAGE A
        JOIN OQQUEUE B ON B.ID = A.QUEUEID
        JOIN OQQUEUE C ON C.ID = A.ORIGINALQUEUEID
        JOIN OQQUEUE D ON D.ID = A.RESENDQUEUEID
        JOIN NJNODE E ON E.ID = A.NODEID
        AND A.STATECODE = 'P' AND NODEID <> 0X0
ORDER BY 
        A.NODEID, A.NAME, A.DELIVERYTIME ASC

The following query shows the queue depth count by queue, state, and message.

SELECT 
        A.NAME OBJECTQUEUE, B.STATECODE, B.NAME MESSAGE, COUNT(1) COUNT
FROM 
        OQQUEUE A
        JOIN OQMESSAGE B ON B.QUEUEID = A.ID
WHERE 
        A.ID NOT IN (SELECT DISTINCT (ERRORQUEUEID) FROM OQOBJECTQUEUE WHERE ERRORQUEUEID IS NOT NULL)
GROUP BY 
        A.NAME, B.STATECODE, B.NAME
ORDER BY 
        A.NAME, B.STATECODE, B.NAME

The following query shows the error queue depth count by queue, state, and message.

SELECT 
        A.NAME ERRORQUEUE, B.STATECODE, B.NAME MESSAGE, COUNT(1) COUNT
FROM 
        OQQUEUE A
        JOIN OQMESSAGE B ON B.QUEUEID = A.ID
WHERE 
        A.ID IN (SELECT DISTINCT (ERRORQUEUEID) FROM OQOBJECTQUEUE WHERE ERRORQUEUEID IS NOT NULL)
GROUP BY 
        A.NAME, B.STATECODE, B.NAME
ORDER BY 
        A.NAME, B.STATECODE, B.NAME

The following query identifes the dispatcher node.

SELECT
         A.*, B.* 
FROM 
         OQOBJECTQUEUEDISPATCHER A
         JOIN NJNODE B ON B.ID = A.NODEID

The following query triggers a dispatcher restart.

UPDATE OQOBJECTQUEUEDISPATCHER SET NODEID = NULL, ADDRESS = NULL;

The following query restricts object queue traffic to a specfic subnet.

UPDATE OQOBJECTQUEUEDISPATCHER SET NETWORK = '0/0' /*ACCEPTS CIDR ADDRESSES*/