About the Data Warehouse sample

The Data Warehouse sample is a message flow sample application that demonstrates a scenario in which a message flow is used to perform the archiving of data, such as sales data, into a database.  The data is stored for later analysis by another message flow or application.

Because the sales data is analyzed at a later date, the storage of the messages has been organized in a way that makes it easy to select records for specified times.  The date and time at which the WebSphere MQ message containing the sales record was written are stored as separate column values when the message is inserted into the database.  The database table contains four columns:

By storing the data in this way it is possible to retrieve records between specific periods of time, say between the hours of 9:00 a.m. to 12:00 p.m.  or 12:01 p.m. and 5:00 p.m. which would allow a comparison of morning and afternoon sales to be made.

Without the use of the additional date and time columns an application would have to read all records from the database, examine them all, process those of interest, and discard the rest. The ability to retrieve records in a controlled and precise manner potentially avoids wasting a large amount of processing that would result if all records had to be read.

The sample illustrates one technique for the archiving of a message, or part of it, into a database. This is the type of processing that would typically be included as part of a more complex message flow when there was a need to archive data.

The processing in the sample consists of two message flows.  The message flows are:

The details of the message flows and the processing they perform are as follows.

WarehouseData message flow

The WarehouseData message flow performs the following processing.

  1. Reads a WebSphere MQ message containing an XML payload.  The payload contains the data to be archived.
  2. Converts a portion of the message tree to a BLOB ready for insertion into the database.
  3. Inserts the message BLOB along with the date and time at which the message WebSphere MQ was written into a database.
  4. Sends a WebSphere MQ confirmation message to signal successful insertion of the message into the database.

The WarehouseData message flow consists of the following nodes:

A screen capture of the WarehouseData message flow.

The MQInput node DATAWAREHOUSE_IN_Q reads the XML message. As the incoming message is in a self-defining XML format there is no need to specify a message set or format for it to be parsed successfully.

The compute node Warehouse_Input_Message converts the whole of the message payload (ROOT.XML) into a BLOB using the ASBITSTREAM function, inserts the message into a database, and finally formats the confirmation message.

The MQOutput node DATAWAREHOUSE_OUT_Q writes the output message as a WebSphere MQ message..

Should an error arise during the database processing a message is formatted in the Compute node Create_Error_Message and written out in the MQOutput node DATAWAREHOUSE_FAILURE_Q as a WebSphere MQ message.

VerifyDatabaseContents message flow

The VerifyDatabaseContents  message flow performs the following processing:

  1. Reads a WebSphere MQ message containing an XML payload that specifies the interval (start and end time) for which a count of records is to be obtained.
  2. Queries the database to determine how many records are present for the times specified.
  3. Formats the reply message.
  4. Writes a WebSphere MQ message.

The VerifyDatabaseContents message flow consists of the following nodes:

A screen capture of the VerifyDatabaseContents message flow.

The MQInput node DATAWAREHOUSE_VERIFY_CONTENTS_IN_Q reads the XML message. As the incoming message is in a self-defining XML format there is no need to specify a message set or format for it to be parsed successfully.

The compute node Verify_Contents performs the following actions:

  1. Extracts the start and end date and time to be used in the database query from the incoming message.
  2. Issues an SQL SELECT statement against the archive database table to determine how records are present in the given time range.
  3. Formats a reply message

The MQOutput node DATAWAREHOUSE_VERIFY_CONTENTS_OUT_Q writes the WebSphere MQ output message.

Should an error arise during the database processing a message is formatted in the Compute node Create_Error_Message and written out in the MQOutput node DATAWAREHOUSE_FAILURE_Q as a WebSphere MQ message.

Test messages

Two messages are used in this sample.  The first is a message that will be archived and the second is a message to specify the date and times to be used by the Verify Database Content message flow. Examples of both messages are show below.

Message to be archived

<SaleEnvelope>
<Header>
<SaleListCount>1</SaleListCount>
</Header>
<SaleList>
<Invoice>
<Initial>K</Initial>
<Initial>A</Initial>
<Surname>Braithwaite</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>
<Invoice>
<Initial>T</Initial>
<Initial>J</Initial>
<Surname>Dunnwin</Surname>
<Item>
<Code>04</Code>
<Code>05</Code>
<Code>01</Code>
<Description>The Origin of Species</Description>
<Category>Books and Media</Category>
<Price>22.34</Price>
<Quantity>02</Quantity>
</Item>
<Item>
<Code>06</Code>
<Code>07</Code>
<Code>01</Code>
<Description>Microscope</Description>
<Category>Miscellaneous</Category>
<Price>36.20</Price>
<Quantity>01</Quantity>
</Item>
<Balance>81.84</Balance>
<Currency>Euros</Currency>
</Invoice>
</SaleList>
<Trailer>
<CompletionTime>12.00.00</CompletionTime>
</Trailer>
</SaleEnvelope>

Message to specify search date and time

<Archive_Query>
<Start_Time>
<Day>mm/dd/yyyy</Day>
<Time>hh:mm:ss</Time>
</Start_Time>
<End_Time>
<Day>mm/dd/yyyy</Day>
<Time>hh:mm:ss</Time>
</End_Time>
</Archive_Query>

Back to sample home