Extending the Message Routing sample

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.

Reusing the Routing_using_memory_cache message flow

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):

Routing_using_memory_cache.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:

  1. The three ESQL variables: Variable1, Variable2, Variable3, are used by the compute node to look up the destination queue manager and queue in the database table. These variables can either be hard-coded or derived from the incoming message. In the sample the first value is hard-coded and the other two are derived from the incoming XML message. It is sensible to have the first value hard-coded so that a different value can be used for each type of message that might be routed. By doing this, one database table can be used for many different sets of the routing data. To create a hard-coded value, set its value in the DECLARE statement. In the sample Variable1 is set to the value of SAMPLE_QUEUES.
  2. This section in the ESQL shows how information from the message can be used to set the other two variables. For a different message type or format this part needs to be completely rewritten to reference the fields in the incoming message that need to be used for routing.
  3. If there are any problems setting the Variables then default values are set. In the sample these are set to the value default.

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.

Changing the scope of the BEGIN ATOMIC ... END; block

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):

moving beging block
  1. The BEGIN ATOMIC signals that the following ESQL is going to be single-threaded until the corresponding "END;" statement is reached.
  2. The ESQL comment marked by the number 4 shows where the "END;" statement can be moved to if the cache is not going to be dynamically refreshed.
  3. The current "END;" statement needs to be removed if a new "END;" is placed at the marker 4 above.

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.

Using external variables to make the message flow easier to deploy to different systems

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:

  1. Modify the ESQL to declare Variable1 as an external variable. Change DECLARE Variable1 CHAR 'SAMPLE_QUEUES' to DECLARE Variable1 EXTERNAL CHAR 'SAMPLE_QUEUES'. The ESQL should look like this:
    -- Section 1
    DECLARE Variable1 EXTERNAL CHAR 'SAMPLES_QUEUES';
    DECLARE Variable2 CHAR;
    DECLARE Variable3 CHAR;
  2. The external variable now needs to be defined at messageflow level. Open the message flow Routing_using_memory_cache and click the User Defined Properties tab at the bottom of the message flow editor. Create a new property called Variable1 and set its default value to SAMPLE_QUEUES:

    User Defined Properties

  3. The variable Variable1 is now an external variable. Add the message flow to a bar file and then click the Configure tab. Select the compute node in the flow and you should be able to change the value for the variable:

    Bar file editor

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).

Changing the cache refresh criteria

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:

  1. Refresh the cache after a given time period
  2. Refresh the cache after a given number of messages

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:

ESQL for criteria

To change the refresh criteria to use a time period of 60 seconds:

  1. Change the criteria marked in the ESQL in red to:

    IF CacheQueueTable.LastUpDate is null or (CURRENT_TIMESTAMP - CacheQueueTable.LastUpDate) second > INTERVAL '60' SECOND THEN

  2. Change SET CacheQueueTable.valid = true; to

    SET CacheQueueTable.LastUpDate = CURRENT_TIMESTAMP;

To change the refresh criteria to be true after 100 messages:

  1. Change the criteria marked in the ESQL in red to:

    IF CacheQueueTable.MessageCount is null or CacheQueueTable.MessageCount > 100 SECOND THEN

  2. Change SET CacheQueueTable.valid = true; to

    SET CacheQueueTable.MessageCount = 0;

  3. Add a statement at the end of the ESQL module to increment the count:

    SET CacheQueueTable.MessageCount = CacheQueueTable.MessageCount +1;

Back to sample home