Extending the Message Routing sample using a database

You can modify the Message Routing sample ESQL so that you can use it in other message flows.

Reusing the Routing_using_database_and_memory_cache message flow

The ESQL file, Routing_using_database_and_memory_cache, contains all of the ESQL that is 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_database_and_memory_cache.esql

You must change the three highlighted parts (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, and 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, which means that 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 you can use information from the message to set the other two variables. For a different message type or format this part must be completely rewritten to reference the fields in the incoming message that are used for routing.
  3. If any problems occur when you are setting the variables, default values are set. In the sample, these values are set to the value default.

When you reuse the ESQL to provide routing capability in another message flow, you can leave the rest of the ESQL. The database table must be updated with any new entries that the new flow requires. See the setupRoutingDatabase database script that is supplied with the sample in the Integration project.

When you are using the ESQL, you must make sure that the Compute Mode on the Compute node properties is set to one of the following values, otherwise the routing information is lost:

The database ODBC source name, Data Source, 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 ensure that one thread only uses the memory cache at a time. The single thread restriction on this part of the ESQL is important only if the cache is going to be refreshed dynamically. If it is decided that the cache does not require refreshing during the life of the message flow, you can reduce the atomic block scope to just cover the initialization of the cache. The following diagram shows the current ESQL (marked as Section 4 in the ESQL):

Moving Begin block
  1. BEGIN ATOMIC signals that the following ESQL is going to be single-threaded until the corresponding END; statement is reached.
  2. If the cache is not being dynamically refreshed, the ESQL comment marked by the number 4 shows to where the END; statement can be moved.
  3. If a new END; is placed at the marker 4, the current END; statement must be removed.

After this modification is done, the look-up of the queue name in the cache is no longer 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 deployment time to the environment to which it is being deployed without having to modify the message flow ESQL.

The Routing_using_database_and_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. This variable must be externalized at deployment time so that its value can be modified depending on the system to which it is being deployed. This externalization allows you to use a different set of queues and queue managers 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 must look like the following example: 
    -- Section 1
    DECLARE Variable1 EXTERNAL CHAR 'SAMPLES_QUEUES';
    DECLARE Variable2 CHAR;
    DECLARE Variable3 CHAR;
  2. The external variable must be defined at message flow level.
    1. Open the Routing_using_database_and_memory_cache message flow, click the User Defined Properties tab at the bottom of the Message Flow editor.
    2. 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.
    1. Add the message flow to a broker archive file (BAR) file, click the Configure tab.
    2. Select the Compute node in the flow and change the value for the following variable:

      BAR file editor

To use this external variable, you must 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 works as before. (Variable1 defaults to SAMPLE_QUEUES).

Changing the cache refresh criteria

The current refresh criteria for the Message Routing sample cache of the database table is:

It is useful if other criteria can be used to decide when to refresh the cache. Possible criteria can be:

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

The sample can be changed to make use of any of these criteria. The critical place in the ESQL for refreshing the cache is:

ESQL for criteria

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

  1. Change the criteria circled in red in the ESQL 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 that is circled in red in the ESQL 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