Page 1 of 10
Examples of using SQL-like operands in the WHERE clause in the runmqsc
DISPLAY command in IBM MQ
https://www.ibm.com/support/pages/node/6566153
Date last updated: 24-Mar-2022
Angel Rivera
IBM MQ Support
https://www.ibm.com/products/mq/support
Find all the support you need for IBM MQ
+++ Objective
You want to be able to apply some SQL-like operands / filters in the WHERE clause in the
runmqsc DISPLAY command in IBM MQ in order to avoid displaying all possible entries for
queues (for example), but displaying only the ones that meet a certain criteria.
This article provides concrete examples for queues and connections.
Question: How to display only the queues that have messages
DISPLAY QLOCAL(*) WHERE(CURDEPTH GT 0)
Question: How to query for all durable subscriptions?
DISPLAY SUB(*) WHERE(DURABLE EQ YES)
Question: How to find out the ones whose DEST queues begin with SYSTEM
DISPLAY SUB(*) WHERE(DEST LK 'SYSTEM*')
Question: How to find out the ones whose DEST queues begin with SYSTEM.MANAGED
DISPLAY SUB(*) WHERE(DEST LK 'SYSTEM.MANAGED*')
Question: How to display the client applications using a server-connection channel:
DISPLAY CONN(*) WHERE(CHANNEL NE '') APPLTAG CHANNEL CONNAME CONNOPTS
Question: How to display only non-system queues (do not display SYSTEM.* queues)
See last page of this document.
Page 2 of 10
+ Introduction
https://hursleyonwmq.wordpress.com/2007/06/13/administration-using-runmqsc-part-2/
Administration using runmqsc, part 2
June 13, 2007
In the previous post, we took a brief look at some of the things we can learn from the
queue manager using the DISPLAY command in runmqsc. It turns out that there is a lot of
information available. So, what if we want to filter the results to see something more
specific?
In WebSphere MQ version 6, 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.
DISPLAY display_cmd
WHERE( filter_keyword operator filter_value )
operator := [ LT | GT | EQ | NE | LE | GE | CT | EX | LK | NL | CTG | EXG ]
+ Reference:
https://www.ibm.com/docs/en/ibm-mq/9.2?topic=reference-display-queue-display-queue-attributes
IBM MQ / 9.2 / DISPLAY QUEUE (display queue attributes)
Section: Parameter descriptions for DISPLAY QUEUE
Parameter:
WHERE
Specify a filter condition to display only those queues that satisfy the selection criterion of
the filter condition.
The filter condition is in three parts: filter-keyword, operator, and filter-value:
filter-keyword
Almost any parameter that can be used to display attributes for this DISPLAY command.
Queues of a type for which the filter keyword is not a valid attribute are not displayed.
operator
This is used to determine whether a queue satisfies the filter value on the given filter
keyword. The operators are:
LT Less than
GT Greater than
EQ Equal to
NE Not equal to
LE Less than or equal to
GE Greater than or equal to
LK Matches a generic string that you provide as a filter-value
NL Does not match a generic string that you provide as a filter-value
Page 3 of 10
filter-value
The value that the attribute value must be tested against using the operator. Depending on
the filter-keyword, this can be:
- An explicit value, that is a valid value for the attribute being tested.
You can use operators LT, GT, EQ, NE, LE or GE only. However, if the attribute value is one
from a possible set of values on a parameter (for example, the value QALIAS on the CLUSQT
parameter), you can only use EQ or NE. For the parameters HARDENBO, SHARE, and
TRIGGER, use either EQ YES or EQ NO.
- A generic value. This is a character string (such as the character string you supply for the
DESCR parameter) with an asterisk at the end, for example ABC*. If the operator is LK, all
items where the attribute value begins with the string (ABC in the example) are listed. If
the operator is NL, all items where the attribute value does not begin with the string are
listed. Only a single trailing wildcard character (asterisk) is permitted.
You cannot use a generic filter-value for parameters with numeric values or with one of a
set of values.
Page 4 of 10
+ Question: How to display only the queues that have messages
In the following article:
https://www.ibm.com/support/pages/node/6209225
Different ways to get the Current Depth (CURDEPTH) for a queue in an MQ queue manager
… see the last item:
d) Administrative MQ REST API for queue and mqsc (requires MQ Web Server 9.1 or higher)
Note:
One refinement to queries that could be helpful to you, is the use of the WHERE clause in
the DISPLAY command.
Here is the runmqsc command that shows all the local queues that have messages, where
the integer attribute for Current Depth is greater than 0 (zero).
DISPLAY QLOCAL(*) WHERE(CURDEPTH GT 0)
Page 5 of 10
+ Question: How to query for all durable subscriptions?
Example:
display sub(*) where(durable eq YES)
AMQ8096: WebSphere MQ subscription inquired.
SUBID(414D5120514D5F414E47454C49544F20F5AF424E20002EE2)
SUB(SUB-T1) DURABLE(YES)
AMQ8096: WebSphere MQ subscription inquired.
SUBID(414D5120514D5F414E47454C49544F20FFF30C4C20001207)
SUB(QM_ANGELITO SYSTEM.BROKER.INTER.BROKER.COMMUNICATIONS
414D515901010000000
00000000000000000000000000000 SYSTEM.BROKER.ADMIN.STREAM MQ/QM_ANGELITO
/StreamSupport)
DURABLE(YES)
AMQ8096: WebSphere MQ subscription inquired.
SUBID(414D5120514D5F414E47454C49544F20EE812B4D20008615)
SUB(subtopic1b) DURABLE(YES)
AMQ8096: WebSphere MQ subscription inquired.
SUBID(414D5120514D5F414E47454C49544F20BB13644D20002D0A)
SUB(JMS:QM_ANGELITO:mqtest1:myTestSubscriptionOPTUM)
DURABLE(YES)
AMQ8096: WebSphere MQ subscription inquired.
SUBID(414D5120514D5F414E47454C49544F207E58314E2000DB03)
SUB(SUB1) DURABLE(YES)
Page 6 of 10
+ Question: How to find out the ones whose DEST queues begin with SYSTEM
NOTES:
- It is OK to NOT use single quotes, because the string does not have a period.
But it is a good practice to always use single quotes.
- Double quotes are not accepted.
display sub(*) where(DEST LK SYSTEM*)
or
display sub(*) where(DEST LK 'SYSTEM*')
AMQ8096: WebSphere MQ subscription inquired.
SUBID(414D51204D46545F4C4E582020202020F55ADD5208790020)
SUB(ANGELILLO - cd3473ad-c52e-42ef-89eb-7fb6478d05bd)
DEST(SYSTEM.MANAGED.DURABLE.52DD5AF520007907)
AMQ8096: WebSphere MQ subscription inquired.
SUBID(414D51204D46545F4C4E582020202020F55ADD52052A0020)
SUB(SYSTEM.FTE.LOG.AUTO.LOGGER_LNX)
DEST(SYSTEM.MANAGED.DURABLE.52DD5AF520002A04)
AMQ8096: WebSphere MQ subscription inquired.
SUBID(414D51204D46545F4C4E5820202020207C1CD852070F0020)
SUB(MFT_LNX SYSTEM.BROKER.INTER.BROKER.COMMUNICATIONS
414D51590101000000000000000000000000000000000000 SYSTEM.BROKER.ADMIN.STREAM
MQ/MFT_LNX /StreamSupport)
DEST(SYSTEM.BROKER.INTER.BROKER.COMMUNICATIONS)
Page 7 of 10
+ Question: How to find out the ones whose DEST queues begin with SYSTEM.MANAGED
NOTES:
- Double quotes are not accepted.
- You MUST use SINGLE quotes.
If you do not single quotes, then you will see a syntax problem due to the period inside the
text.
display sub(*) where(DEST LK 'SYSTEM.MANAGED*')
14 : display sub(*) where(DEST LK 'SYSTEM.MANAGED*')
AMQ8096: WebSphere MQ subscription inquired.
SUBID(414D51204D46545F4C4E582020202020F55ADD5208790020)
SUB(ANGELILLO - cd3473ad-c52e-42ef-89eb-7fb6478d05bd)
DEST(SYSTEM.MANAGED.DURABLE.52DD5AF520007907)
AMQ8096: WebSphere MQ subscription inquired.
SUBID(414D51204D46545F4C4E582020202020F55ADD52052A0020)
SUB(SYSTEM.FTE.LOG.AUTO.LOGGER_LNX)
DEST(SYSTEM.MANAGED.DURABLE.52DD5AF520002A04)
Page 8 of 10
+ Question: How to display the client applications using a server-connection channel
For more information see the following tutorial:
http://www.ibm.com/support/docview.wss?uid=swg27045669
How to identify MQ client connections and stop them
(Tutorial)
- To display the client applications using a server-connection channel:
display conn(*) where(channel NE '') APPLTAG CHANNEL CONNAME CONNOPTS
You want to use runmqsc "display conn(*)" and to display ONLY those entries that have a
channel name, that is, you do not want to see the entries that do not have a channel name.
The query is similar to an SQL "where channel name is not null".
But the WHERE capability for the DISPLAY command does not have explicitly the "is not null"
argument. Is there an equivalent argument?
a) Display connection where channel name "is not null":
Yes, you can use the WHERE argument for "is not null":
Generic: display MQOBJECT where(attribute NE '')
**
Notice that it is a single quote repeated twice (it is 2 characters). It is NOT a single
character that is the double quote.
This type of query is useful to find out the connections for server-connection channels.
For example, the following query is for "channel name is NOT null) and will show a subset of
the attributes:
display conn(*) where(channel NE '') APPLTAG CHANNEL CONNAME
**
An example of the output is shown below. Notice that there is a name value for the
attribute CHANNEL.
AMQ8276: Display Connection details.
CONN(B0973655021E0020)
EXTCONN(414D5143514D5F373520202020202020)
TYPE(CONN)
APPLTAG(amqsputc) CHANNEL(SYSTEM.DEF.SVRCONN)
CONNAME(9.27.47.38)
Page 9 of 10
b) Display connection where channel name "Is null":
Just for completeness, you can use the WHERE argument for "is null":
Generic: display MQOBJECT where(attribute EQ '')
**
Notice that it is a single quote repeated twice (it is 2 characters). It is NOT a single
character that is the double quote.
For example, the following query is for "channel name is null) and will show a subset of the
attributes:
display conn(*) where(channel EQ '') APPLTAG CHANNEL CONNAME
**
An example of the output is shown below. Notice that there is not a name value for the
attribute CHANNEL.
display conn(*) where(channel EQ '') APPLTAG CHANNEL CONNAME
AMQ8276: Display Connection details.
CONN(B0973655010B0020)
EXTCONN(414D5143514D5F373520202020202020)
TYPE(CONN)
APPLTAG(amqzmuf0) CHANNEL( )
CONNAME( )
Page 10 of 10
+ Question: Displaying only non-system queues (do not display SYSTEM.* queues)
You wish to see all non-system queues (for example LOCAL QUEUES) using DISPLAY
command.
It is not possible to do this query with the SQL-like facilities in the WHERE clause.
The following was found in a forum of MQ users, and it is added here for completeness.
.
For Unix (it is a SINGLE line – very long line!):
echo "dis ql(*)" | runmqsc QM92 | grep QUEUE | tr '()' ' ' | tr -s ' ' | cut -f3 -d' ' | grep -v ^SYSTEM | xargs -I %%
echo "dis ql(%%)" | runmqsc QM92
+++ end
