The Message Routing sample shows how a database table can be used to store routing information that a message flow can use to route messages to WebSphere MQ queues. This is an alternative approach to creating hard-coded routing information in a message flow compute node.
Using a database table is the recommended way in WebSphere Message Broker to store any form of routing or transformation data for the following reasons:
The one problem with using databases to store this information is the performance overhead that is incurred to access the database each time a message passes through a message flow.
Before version 6.0 of WebSphere Message Broker, a design decision had to be made between hard coding routing or mapping information in ESQL, or using a database table to store the information. The decision would often be based on whether the performance cost of using the database was acceptable. For high message rates, it was often decided to create hard-coded information that would be easier to maintain in a database table.
In version 6.0 the concept of shared variables was introduced. With shared variables you can store state in message flows that is available to all messages using the flow. The exact details of how shared variables are used can be found in DECLARE variables. Using shared variables, database tables can be stored in-memory in a message flow. This removes the need for continued access to the database table, and gives almost all the advantages of using a database table without the performance overhead. Only one advantage is missing: the cache will not pick up changes to the database table as they happen. To pick the changes up, either you need to restarted the message flow or you need to add a refresh mechanism to the message flow.
The Message Routing sample shows how to use a database table to route messages in a message flow, and how a message flow can use shared variables to store the database table. It also shows how the cached database table can be refreshed by restarting the message flow or by sending a refresh message to the message flow.
The Message Routing sample contains two message flows:
A sample of how to route a message using a database table. This simple message flow reads a message from an WebSphere MQSeries queue, creates a destination list based on data in a database table and then routes the message to the entries in the destination list.
A sample of how to route a message using a cached version of the database table stored in shared variables. The message flow has two main subflows. The first subflow gives the same functionality as the Routing_using_database_table message flow but using shared variables. The second subflow is used to refresh the shared memory variables.
The test message used to drive the Message Routing sample is a straightforward XML message that contains invoice details for a customer
<SaleEnvelope> <Header> <SaleListCount>1</SaleListCount> </Header> <SaleList> <Invoice> <Initial>T</Initial> <Initial>D</Initial> <Surname>Montana</Surname> <Item><Code>00</Code> <Code>01</Code><Code>02</Code> <Description>Twister</Description> <Category>Games</Category> <Price>00.30</Price> <Quantity>01</Quantity> </Item> <Item> <Code>02</Code><Code>03</Code><Code>01</Code> <Description>The Times Newspaper</Description> <Category>Books and Media</Category> <Price>00.20</Price> <Quantity>01</Quantity> </Item> <Balance>00.50</Balance> <Currency>Sterling</Currency> </Invoice> </SaleList> <Trailer> <CompletionTime>12.00.00</CompletionTime> </Trailer> </SaleEnvelope>
The <Initial> and <Surname> fields are used to do the database lookup to find the destination queue for each customer.
The sample message flows use the same database table to get the routing information from. The database table contains five columns: the first three contain look-up information that is derived from the incoming message and the last two columns contain the details of the queue and queue manager to which the message is routed. The columns are:
Column | Description |
Variable1 | The first variable used in looking up the destination queue (This is hard-coded in the sample and is not derived from the XML message) |
Variable2 | The second variable used in looking up the destination queue (This is derived from the XML message using the <Initial> field) |
Variable3 | The third variable used in looking up the destination queue (This is derived from the XML message using the <Surname> field) |
Queue_manager | The destination queue manager name |
Queue_name | The destination queue name |
The definition for the database table is provided in the sample message flow project in the Database directory. The database table is not directly linked to the incoming XML message so it would be possible to reuse this table for lots of different routing messages. If this was the case, then the first variable could be used to differentiate between different nodes using the same table.
Each of the message flows compute nodes for the message flows contains detailed ESQL comments explaining exactly what has been done to achieve the required behavior:
How you view the contents of the sample's database depends on which database product is installed. For DB2 databases use the tools provided by DB2; for example, the DB2 Control Center. If you have not installed a database product, the samples use the Apache Derby database. For instructions on how to view the data in a Derby database see Viewing the contents of a Derby database.