=IF(ISNA(VLOOKUP(A1,$B$1:$B$6,1,FALSE)), "No", "Yes")
A is a list,
B is a List
if Values in A are in the B List, have Yes in the third column...
A is a list,
B is a List
if Values in A are in the B List, have Yes in the third column...
A B C, they may not arrive in that same order if, for example, the intervening network distributes the messages across a cluster and then recombines them. But what if message order is vital to the functioning of the application?
Imagine a scenario in which message B tells the application to ignore the previous message.
The meaning of the sequence would then be very different if the messages arrived in the order C B A.A, B and C as part of a group.
Each message in the group is assigned a sequence number starting at 1. The receiving application can then specify that it wishes to receive the messages
in this logical order, as opposed to the physical order in which the messages arrive at a destination. Now, even if message B or C arrives first, they will not be passed to the application immediately because they do not have sequence number 1.default on the queue manager QM_host:MQQueueManager queueManager = new MQQueueManager("QM_host");
MQQueue queue = queueManager.accessQueue("default", MQC.MQOO_OUTPUT);
MQPutMessageOptions pmo = new MQPutMessageOptions();
pmo.options = MQC.MQPMO_LOGICAL_ORDER;
for (int i = 1; i <= 5; i++) {
MQMessage message = new MQMessage();
message.format = "MQSTR";
message.writeString("Message " + i);
if (i < 5) {
message.messageFlags = MQC.MQMF_MSG_IN_GROUP;
} else {
message.messageFlags = MQC.MQMF_LAST_MSG_IN_GROUP;
}
queue.put(message, pmo);
}
queue.close();
queueManager.disconnect();
MQPMO_LOGICAL_ORDER is added to the put message options.
This value indicates to the queue manager that the application is going to put each of the messages in the group in sequential order
and that all of the messages in one group will be put by this client before any of the messages in the next.MQSTR so that we may later receive the messages as JMS text messages.) For the first four messages, the message flag MQMF_MSG_IN_GROUP is set to indicate that the message should be part of the current group.
The fifth message has the message flag MQMF_LAST_MSG_IN_GROUP set to indicate that it is the final message in the group.
The next time a message is put with the MQMF_MSG_IN_GROUP flag, a new group will automatically be started.
MQPMO_LOGICAL_ORDER put message option is purely for convenience. It is possible for an application
to not use this flag and to set the group identifier and sequence numbers explicitly, which may be necessary if messages are to be sent out of order
or interleaved with other message groups. The message flags should still be set to indicate that a message is in a group and whether it is the last message.
Another scenario where this may be useful is if the messages in a group are spread over a long period of time.
An application may send the first few messages in a group using logical message ordering, after which the system fails.
When the application restarts, it can continue with the message group by sending the next message without logical ordering and explicitly set the group identifier to be the one used for the earlier messages along with the next sequence identifier. At this point, it may switch back to using logical message ordering for the subsequent messages. The queue manager will then continue to use the same group identifier and increment the sequence number each time.MQQueueManager queueManager = new MQQueueManager("QM_host");
MQQueue queue = queueManager.accessQueue("default", MQC.MQOO_INPUT_AS_Q_DEF);
MQGetMessageOptions gmo = new MQGetMessageOptions();
gmo.options = MQC.MQGMO_LOGICAL_ORDER | MQC.MQGMO_ALL_MSGS_AVAILABLE;
gmo.matchOptions = MQC.MQMO_NONE;
MQMessage message = new MQMessage();
do {
queue.get(message, gmo);
int dataLength = retrievedMessage.getDataLength();
System.out.println(message.readStringOfCharLength(dataLength));
gmo.matchOptions = MQC.MQMO_MATCH_GROUP_ID;
} while (gmo.groupStatus != MQC.MQGS_LAST_MSG_IN_GROUP);
queue.close();
queueManager.disconnect();
MQGMO_LOGICAL_ORDER indicates that we wish to receive the messages in logical order, in other words, the message with sequence number 1 should be received first, then number 2, and so on. The second option,
MQGMO_ALL_MSGS_AVAILABLE, indicates that we don't wish to receive any messages in a group until all of the messages are available.
This option prevents us from starting to process messages in a group only to discover that subsequent messages haven't been sent yet, let alone arrived.get, we specify that no match options are required -- we are prepared to receive the first message in any group.
For subsequent iterations, we specify the MQMO_MATCH_GROUP_ID option, which indicates that we only wish to receive a message
with a matching group identifier. We reuse the same message object for each iteration and consequently, for the second get, it will contain the group identifier
of the first message received. Each get updates the group status field of the get message options.
When it is set to MQGS_LAST_MSG_IN_GROUP, we know that we have received all of the messages in the group.JMSXGroupID and JMSXGroupSeq,
and specifies that they represent the identity of the group a message is part of, and the sequence number within that group. The JMS specification does not, however, provide any support for using these properties.
All is not lost though -- with a few workarounds, we can still replicate our existing behavior using these properties.MQPMO_LOGICAL_ORDER was simply an instruction to the queue manager to automatically allocate message group identifiers and sequence numbers. The example in Listing 3 below
demonstrates how, in the absence of this option in the JMS API, we can set these properties explicitly. MQConnectionFactory factory = new MQConnectionFactory();
factory.setQueueManager("QM_host")
MQQueue destination = new MQQueue("default");
destination.setTargetClient(JMSC.MQJMS_CLIENT_NONJMS_MQ);
Connection connection = factory.createConnection();
Session session = connection.createSession(false, Session.AUTO_ACKNOWLEDGE);
MessageProducer producer = session.createProducer(destination);
String groupId = "ID:" + new BigInteger(24 * 8, new Random()).toString(16);
for (int i = 1; i <= 5; i++) {
TextMessage message = session.createTextMessage();
message.setStringProperty("JMSXGroupID", groupId);
message.setIntProperty("JMSXGroupSeq", i);
if (i == 5) {
message.setBooleanProperty("JMS_IBM_Last_Msg_In_Group", true);
}
message.setText("Message " + i);
producer.send(message);
}
connection.close();
BigInteger and converting it to a hexadecimal string.
As with message identifiers, the WebSphere MQ JMS API expects the group identifier to be prefixed by the string ID:JMSXGroupId string property and the sequence number to the JMSXGroupSeq integer property. The API assumes that if the group identifier is set, then the message is part of a group.
Therefore all that remains is to indicate the last message in the group, which we do by setting the Boolean property
JMS_IBM_Last_Msg_In_Group to true.MQJMS_CLIENT_NONJMS_MQ in Listing 3 above,
which ensures that an RFH2 header is not added to the body of the message, which would confuse the non-JMS client.JMS_IBM_Last_Msg_In_Group property set.
The hard part is trying to reproduce the behavior of the MQGMO_ALL_MSGS_AVAILABLE option. Listing 4 shows one possible solution:MQConnectionFactory factory = new MQConnectionFactory();
factory.setQueueManager("QM_host")
MQQueue destination = new MQQueue("default");
Connection connection = factory.createConnection();
connection.start();
Session session = connection.createSession(true, Session.AUTO_ACKNOWLEDGE);
MessageConsumer lastMessageConsumer =
session.createConsumer(destination, "JMS_IBM_Last_Msg_In_Group=TRUE");
TextMessage lastMessage = (TextMessage) lastMessageConsumer.receiveNoWait();
lastMessageConsumer.close();
if (lastMessage != null) {
int groupSize = lastMessage.getIntProperty("JMSXGroupSeq");
String groupId = lastMessage.getStringProperty("JMSXGroupID");
boolean failed = false;
for (int i = 1; (i < groupSize) && !failed; i++) {
MessageConsumer consumer = session.createConsumer(destination,
"JMSXGroupID='" + groupId + "'AND JMSXGroupSeq=" + i);
TextMessage message = (TextMessage)consumer.receiveNoWait();
if (message != null) {
System.out.println(message.getText());
} else {
failed = true;
}
consumer.close();
}
if (failed) {
session.rollback();
} else {
System.out.println(lastMessage.getText());
session.commit();
}
}
connection.close();
JMS_IBM_Last_Msg_In_Group=TRUE. If the messaging topology is such that ordering cannot be guaranteed, then we cannot be 100% sure that all of the other messages in the group have arrived. In a moment we'll see how we deal with the case when they have not all arrived.null back from the receiveNoWait method. At this point we set the failed flag, which causes us to drop out of the loop.true to indicate that the send and receive operations performed on this session should be performed as part of a local transaction.
This means that, if the failed flag has been set because a message has not been received, we can roll back the transaction
and return all of the other messages in the group back to the queue. If we did successfully receive all of the messages,
then we must remember to commit the transaction. Otherwise, when we close the connection, the transaction will be rolled back on our behalf.JMS_IBM_Last_Msg_In_Group=TRUE so that the MDB is always passed the last message in a group.
We need this first message to be received as part of a transaction, and therefore the MDB should also be configured to use Container Managed Transactions (CMT)
with a transaction attribute of RequiresNew. Here is the code for the onMessage method of our MDB:public void onMessage(Message lastMessage) {
InitialContext context = new InitialContext();
ConnectionFactory factory =
(ConnectionFactory) context.lookup("java:comp/env/jms/factory");
Destination destination =
(Destination) context.lookup("java:comp/env/jms/destination");
Connection connection = factory.createConnection();
connection.start();
Session session = connection.createSession(false, Session.AUTO_ACKNOWLEDGE);
int groupSize = lastMessage.getIntProperty("JMSXGroupSeq");
String groupId = lastMessage.getStringProperty("JMSXGroupID");
boolean failed = false;
for (int i = 1; (i < groupSize) && !failed; i++) {
MessageConsumer consumer = session.createConsumer(destination,
"JMSXGroupID='" + groupId + "'AND JMSXGroupSeq=" + i);
TextMessage message = (TextMessage) consumer.receiveNoWait();
if (message != null) {
System.out.println(message.getText());
} else {
failed = true;
}
consumer.close();
}
if (failed) {
_context.setRollbackOnly();
Thread.sleep(5000);
} else {
System.out.println(((TextMessage) lastMessage).getText());
}
connection.close();
}