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.
The WarehouseData message flow performs the following processing.
The WarehouseData message flow consists of the following nodes:
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.
The VerifyDatabaseContents message flow performs the following processing:
The VerifyDatabaseContents message flow consists of the following nodes:
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:
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.
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.
<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>