I am trying to set up a JMS Message Selector for a trigger that will only pick up messages say 15 minutes or older. I can use a simple query such as JMSTimestamp > 12345… and messages with timestamps larger than the one I hard coded in will be picked up, so I know that part works. I however want to pick up messages older than the current time minus 15 minutes.
From the Built-in reference I saw this “A message selector allows a client to filter the messages it wants to receive by use of a SQL92 string expression in the message header.”. The JMS timestamp is in Unix time while the SQL92 CURRENT_TIMESTAMP comes in a more human readable format. I have seen SQL92 examples online where people will attempt to convert the current timestamp into Unix format with something like SELECT DATEDIFF(s, ‘1970-01-01 00:00:00’, CURRENT_TIMESTAMP)
The problem is I cannot get this to work in the JMS Message Selector field despite the doc saying SQL92 IS supported. Even putting something simple in such as JMSTimestamp > SELECT DATEDIFF (‘2000-01-10’, ‘2000-01-05’); which is the equivalent of JMSTimestamp > 5 did not work.
Any help or other approaches I can take? Perhaps there is a more simple way to pickup messages older than a certain amount of time.
is there a specific use case why you want to retrieve the messages not direclty after they have been inserted into the Queue/Topic, but with a delay of 15 minutes?
We are using Wm v9.9. This came up because we were bouncing around ideas to handle reprocessing. One idea is to place a message into a queue that failed to send originally. After that a job will run say every hour that triggers a service to pick up those messages and attempt to send them again. If they fail again they are placed back into the original queue they were picked up from.
Without some sort of selector, if the messages continue to fail when sending the service will be stuck in a loop continually picking up the message, trying to send, failing, and then picking up immediately again. Selecting by an age timestamp would help prevent this.
Is it possible to do? Is there a reason some SQL92 commands do not work despite the doc saying they should?
might be possible, but I have never used such a scenario.
This also depends if the order of the messages in this queue needs to be pertained or not.
If yes, this wont work as you might receive a first message failing and a second message relying on the first one which has not been processed, which might lead to data corruption.
Either the message processing is successful or not.
If not, we check why it has failed.
If it is due to validation error we inform the sender by putting it into an error queue and sending them a notification with the description what failed. The message is discarded from regular processing afterwards.
If it is due to technical transport error we revert it back to the queue (just not acknowledging it), suspend the trigger and retry later. When retries exceed partner systems are informed and message is discarded.
there is a field in _env part of the message called age. This value is a counter for the number of reprocessing.
As for us the ordering of the messages and therefore the dependencies between them is important, we leave the handling to the trigger and only do some notifications to our operatings team and the partner systems that there was something weird with the message so we can investigate and hopefully correct this.