The Message Routing sample ESQL is designed in such a way that it can be modified to be used in other message flows with very few changes.
The ESQL file Routing_using_memory_cache contains all the ESQL used in the cached version of the sample. Open this file and find the following section (marked as Section 1 in the ESQL):
You need to change the three parts high lighted (sections 1, 2 and 3) based on the message that is going to be routed by the ESQL:
You can leave the rest of the ESQL unchanged when you reuse it to provide routing capability in another message flow. The database table has to be updated with any new entries that the new flow requires. See the database script setupRoutingDatabase that is supplied with the sample in the message flow project.
The one important thing that must be done when using the ESQL is to make sure that the mode on the compute node properties is set to one of the following values, otherwise the routing information will be lost:
The database ODBC source name must also be added to the compute node properties.
The BEGIN ATOMIC ... END; statement is used in the Routing_using_memory_cache message flow to make sure that only one thread uses the memory cache at a time. The single thread restriction on this part of the ESQL is only important if the cache is going to be refreshed dynamically. If it is decided that the cache does not need to be refreshed during the life of the message flow then the atomic block scope can be reduced to just cover the initialization of the cache. The following figure shows the current ESQL (marked as Section 4 in the ESQL):
After this modification is done, the look-up of the queue name in the cache will no longer be single-threaded. Several different messages can read from the cache at the same time.
External variables allow hard coded values in message flows to be promoted to the message flow level so that they can be modified at deploy time. The message flow can be customized at deploy time to the environment that it is being deployed to without having to modify the message flow ESQL.
The Routing_using_memory_cache message flow has a variable called
Variable1, which is used to do the database lookup. It is hard-coded to
the value SAMPLE_QUEUES
.
It makes sense for this variable to be externalized at deploy time
so that its value can be modified depending on the system being
deployed to. This then allows a different set of queues and queue
managers to be used
for each system, but still allows the same database table to be used.
To make Variable1 an external variable:
-- Section 1
DECLARE Variable1 EXTERNAL CHAR 'SAMPLES_QUEUES';
DECLARE Variable2 CHAR;
DECLARE Variable3 CHAR;
To use this external variable you need to make new entries in the ROUTING database ROUTING_TABLE table which has different Variable1 parameters. If the flow is deployed without changing the value of Variable1, then it should work as before. (Variable1 should default to SAMPLE_QUEUES).
The current refresh criteria for the Message Routing sample cache of the database table is:
It would be useful if other criteria could be used to decide when to refresh the cache. Possible criteria could be:
The sample can easily be changed to make use of any of these criteria. The critical place in the ESQL for the refreshing of the cache is:
To change the refresh criteria to use a time period of 60 seconds:
IF CacheQueueTable.LastUpDate is null or (CURRENT_TIMESTAMP - CacheQueueTable.LastUpDate) second > INTERVAL '60' SECOND THEN
SET CacheQueueTable.LastUpDate = CURRENT_TIMESTAMP;
To change the refresh criteria to be true after 100 messages:
IF CacheQueueTable.MessageCount is null or CacheQueueTable.MessageCount > 100 SECOND THEN
SET CacheQueueTable.MessageCount = 0;
SET CacheQueueTable.MessageCount = CacheQueueTable.MessageCount +1;