Filters in MQ using the Where clause :
In WebSphere MQ version 6 and later, the WHERE clause was
introduced. This SQL-like expression allows an administrator to select a subset
of results from the output of the DISPLAY command, based on the state of specific
attributes. Users who have been using runmqsc for a long time, or who are new
to the product, may not be aware of some of the things that are possible using
this extended syntax.
The structure of the WHERE clause is WHERE(<attribute>
<state> <value>) – for <state> we can substitute GT for
Greater Than, LT for Less Than, EQ for Equals, and so on. You can find out more
about the structure of the filter condition in the Infocenter.
In previous versions of WMQ, we could quickly find out how
many messages were on a range of queue using DISPLAY QLOCAL(*) CURDEPTH.
Unfortunately, that would show all of the queues. Of course, we could add a
simple filter on part of the queue name to exclude the SYSTEM queues, using DIS
QL(OURAPP.*). Now though, we can be a lot more specific.
For example, to show
all of the local queues whose depth is greater than 2, we could use
WHERE(CURDEPTH GT 2):
dis ql(*) where (curdepth gt 2)
34 : dis ql(*)
where (curdepth gt 2)
AMQ8409: Display Queue details.
QUEUE(SYSTEM.AUTH.DATA.QUEUE) TYPE(QLOCAL)
CURDEPTH(85)
AMQ8409: Display Queue details.
QUEUE(SYSTEM.CLUSTER.REPOSITORY.QUEUE)
TYPE(QLOCAL) CURDEPTH(45)
AMQ8409: Display Queue details.
QUEUE(XML) TYPE(QLOCAL)
CURDEPTH(3)
Now, what about the case were we want to know which of our
channels are having problems. Which channels are currently in retry?
dis chs(*) where (status eq RETRYING)
32 : dis chs(*)
where (status eq RETRYING)
AMQ8417: Display Channel Status details.
CHANNEL(TO.CLUSQM) CHLTYPE(CLUSSDR)
CONNAME(localhost(1414)) CURRENT
RQMNAME( ) STATUS(RETRYING)
SUBSTATE( )
XMITQ(SYSTEM.CLUSTER.TRANSMIT.QUEUE)
Here is a general scenario : messages are mysteriously
“disappearing” from a particular queue, but you don’t know which application is
reading from it. Below filter gives us
which application is actually consuming the messages.
Here’s a really handy command. We’ll analyse it afterwards.
dis conn(*) where (appltype eq USER) pid connopts appltag
userid channel
28 : dis conn(*)
where (appltype eq USER) pid connopts appltag userid channel
AMQ8276: Display Connection details.
CONN(D4E4634620001002)
EXTCONN(414D51434C4142325F514D2020202020)
TYPE(CONN)
PID(3784)
APPLTAG(WebSphere
MQ\bin\amqsput.exe)
APPLTYPE(USER) CHANNEL( )
CONNOPTS(MQCNO_SHARED_BINDING) USERID(mqtester)
Let’s just review what we asked for here.
We’ve asked the queue manager to tell us about all (*) of
the CONNections it knows about, WHERE the APPLTYPE [application type] is USER –
this means that the results will exclude the MQ queue manager system processes
like the channel initiator, the channel agent, the listener, and so on.
We’ve then limited the information displayed to show only
the PID [process ID], CONNOPTS [MQ connection options], APPLTAG [name of the
binary], USERID [user that the process is running under] and CHANNEL [channel
that the application is connecting over, if applicable]. There are a bunch of
other attributes on the CONN object, but are not relevant to our scenario.
We can play around with the WHERE clause on DISPLAY. By
combining the new queue manager objects in WebSphere MQ version 6 and filtering
the results using WHERE, it is possible to troubleshoot much more quickly.
Comments