Running the Message Routing sample using a database

Running the Message Routing sample is split into two scenarios:

Using a direct connection to the database table to retrieve routing information for each message that is processed
Using shared variables to store a copy of the database table in the message flow that can be made use of by each message that is processed

These two scenarios are described separately. All of the test messages that are used in this section are stored in the Test messages directory in the Message routing sample message flows Integration project.

If you encounter any problems when you run the sample, see Resolving problems when running samples in the IBM Integration Bus documentation.

Input test messages

All of the test messages that are used to run this sample are based on the following format:

<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 for routing the message.

Running the sample

Using a direct connection to the database table to route the message

This part of the sample uses the Routing_using_database_table message flow. The message flow contains one input queue called ROUTING.DATABASE.IN1. The message flow can route the message to one of 20 output queues based on the content of the message (ROUTING.OUT followed by a number from 1-19 plus a queue called ROUTING.DEFAULT). To run this part of the sample:

In the Application Development view, expand the Message routing sample message flows project. Under Flow Tests, double-click Database_Direct_message1.mbtest to open the file in the Test Client.
In the Test Client, click Enqueue, click Send Message to send a message to the ROUTING.DATABASE.IN1 queue. The message flow routes the message to the ROUTING.OUT1 queue.
In the Test Client, click Dequeue, click Get Message to read a message from the ROUTING.OUT1 queue.
Under Test messages, double-click Database_Direct_message2.mbtest to open the file in the Test Client.
In the Test Client, click Enqueue, click Send Message to send the message to ROUTING.DATABASE.IN1. The message flow routes the message to the ROUTING.OUT19 queue.
In the Test Client, click Dequeue, click Get Message to read the routed message from the ROUTING.OUT19 queue.

If all of the preceding steps are successful, the first part of the sample is complete. To find out how the routing is programed, look at the ESQL in the message flow, see Routing_using_database_table_Compute.

Using an in-memory cache of the database table to route the message

This part of the sample makes use of the Routing_using_memory_cache message flow. It contains two input queues called ROUTING.DATABASE.MEMORY.IN1 and ROUTING.REFRESH.IN1. The message flow can route the message to one of 20 queues based on the content of the message (ROUTING.OUT followed by a number from 1-19 plus a queue called ROUTING.DEFAULT). The routing logic is the same as in the non-cached version, the difference is that the database is cached in-memory by using shared variables. Use the following steps to run this part of the sample:

In the Application Development view, expand the Message routing sample message flows project. Under Test messages, double-click Database_Memory_cache_message1.mbtest to open the file in the Test Client.
In the Test Client, click Enqueue, click Send Message to write the message to the ROUTING.DATABASE.MEMORY.IN1 queue.
In the Test Client, click Dequeue, click Get Message to get the message from the ROUTING.OUT1 queue.
Under Test messages, double-click Database_Memory_cache_message2.mbtest to open the file in the Test Client.
In the Test Client, click Enqueue, click Send Message to write the message to the ROUTING.DATABASE.MEMORY.IN1 queue.
In the Test Client, click Dequeue, click Get Message to get the message from the ROUTING.OUT19 queue.
Up to this point the flow is behaving exactly the same as the last flow as far as functionality is concerned, but this flow is using a cached version of the database table. To test the effect of using the cache, the database table is modified to show how the in memory cache can be refreshed. To modify the database table for your database:
- DB2
  1. Run the command:
    - On Windows: Enter the following command in a DB2 command window:
```
db2cmd
```
    - On Linux: Enter the following command in a terminal window:
```
db2
```
  2. Connect to the ROUTING database:
```
DB2> connect to ROUTING user user using password
```
    Where user is a user that has the authority to access the ROUTING database, typically the same user that was used to create the sample integration node; and password is the password that is used for that user.
  3. Update the last entry in the routing_table to Send Message ROUTING.OUT18:
```
DB2> update routing_table set queue_name='ROUTING.OUT18' where Variable3='Braithwaite'
```
  4. Disconnect the ROUTING database:
```
DB2> disconnect ROUTING
```
- Oracle
  1. Enter the following command in a command window:
```
sqlplus <user>/<password>
```
    Where user is a user that has the authority to access the routing_table table, typically the same user that was used to create the sample integration node; and password is the password that is used for that user.
  2. Update the last entry in the routing_table to Send Message ROUTING.OUT18:
```
update routing_table set queue_name='ROUTING.OUT18' where Variable3='Braithwaite';
```
Double-click Database_Memory_cache_message2.mbtest to open it in the Test Client.
In the Test Client, click Enqueue, click Send Message to write the message to the ROUTING.DATABASE.MEMORY.IN1 queue.
In the Test Client, click Dequeue, click Get Message to get the message from the ROUTING.OUT19 queue. The message is not routed to the new queue name because the database table has not been reread.
To make the flow reread the database, under Test messages open Database_Refresh_cache_message1.mbtest, click Enqueue, click Send Message to write the message to the ROUTING.DATABASE.REFRESH.IN1 queue.
Double-click Database_Memory_cache_message2.mbtest to open it in the Test Client, click Enqueue, click Send Message to write the message to the ROUTING.DATABASE.MEMORY.IN1 queue.
In the Test Client, click Dequeue, click Get Message to read the routed message from the ROUTING.OUT18 queue.

Steps 9-13 can be repeated by changing the queue_name in the database to one of the other queue names that are available. Instead of using the Database_Refresh_cache_message1 message you can stop and restart the message flow, which also causes the cached database table to refresh.

If all of the preceding steps are successful, the sample is complete. To find out how to store the database in shared variables, look at the ESQL in the message flow, see Routing_using_database_and_memory_cache_Compute.

Understanding the results

The Message Routing sample routes the input test message to various output queues without modifying the output. The output message is identical to the input test message, see Input test messages. The steps given in the two scenarios detail to where the messages are routed. You can check that the messages are routed to the correct queue by using WebSphere MQ Explorer as well as by using Dequeue in the Test Client.

Back to sample home