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:
The WarehouseData message flow performs the following processing:
The WarehouseData message flow consists of the following nodes:
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.
The VerifyDatabaseContents message flow performs the following processing:
The VerifyDatabaseContents message flow consists of the following nodes:
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:
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.
Two messages are used in this sample:
The following examples show both types of message.
<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>
<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>