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 is organized in a way that makes it easy to select records for specified times. When the message is inserted into the database, the date and time at which the WebSphere MQ message containing the sales record was written are stored as separate column values. The database table contains four columns:

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

Without the use of the additional date and time column, an application has to read all of the 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, which is the result if all of the records have to be read.

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

The processing in the sample consists of two message flows:

The details of the message flows and the processing they perform are shown in the following sections:

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 archive.
  2. Converts a portion of the message tree to a BLOB ready for insertion into the database.
  3. Inserts the BLOB message and the date and time at which the WebSphere MQ message 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 Data_Warehouse_In_Q MQInput node reads the XML message. Because the incoming message is in a self-defining XML format, no requirement to specify a message set or format for it to be parsed successfully exists.

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

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

If an error arises during the database processing, a message is formatted in the Create_Error_Message Compute node and written out in the Data_Warehouse_Failure_Q MQOutput node 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 Data_Warehouse_Verify_Contents_In_Q MQInput node reads the XML message. Because the incoming message is in a self-defining XML format, no requirement to specify a message set or format for it to be parsed successfully exists.

The Verify_Contents Compute node performs the following actions:

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

The Data_Warehouse_Verify_Contents_Out_Q MQOutput node writes the WebSphere MQ output message.

If an error arises during the database processing, a message is formatted in the Create_Error_Message Compute node and is written out in the Data_Warehouse_Failure_Q MQOutput node as a WebSphere MQ message.

Test messages

Two messages are used in this sample:

The following examples show both types of message.

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