Specifying Message Order When Using Multiple Tables |
If you are generating messages from more than one database table, you can ensure that the messages are generated in a specific order:
- When creating the From Database source component for your channel, select the Order messages by column check box.
- In the Column field, type the name of the table column to use for sorting. This column must be defined in each of the databases from which messages are generated.
- From the list box next to this field, select Ascending or Descending to specify the sort order.
- In your database tables, ensure that each table includes the MESSAGE_ID field.
- Verify that the data in your tables is in the order in which you want it to be processed. To do this, look at each row of your tables and examine the value of the field that is used for sorting.
If two or more table rows contain the same sort value, the resulting outbound messages are sent in the order in which they are processed.
 | ||||||||||||||||||||
|
Every time Iguana polls for a message from the database, it executes a SELECT query on each message that was defined in the VMD file. The SELECT query uses the MIN() or MAX() function (for ascending or descending, respectively) to retrieve exactly one row from each table (no limits required). Each table row corresponds to one message that is waiting to be sent. When Iguana iterates through all the retrieved messages, it examines the column that is used to specify the sort order, and therefore can determine which message contains the minimum or maximum value in this column. For example, suppose that there are three message definitions in a VMD file, sorting is by ascending order, and MESSAGE_ID is the column that is used for sorting. Now suppose that Iguana retrieves the following messages:
Iguana now calculates the global value as follows:
Because MessageB has the MESSAGE_ID column with the smallest value, Iguana sends MessageB. After sending this message, Iguana re-executes the queries and sends MessageA. Re-executing the queries will handle situations where you decide, for example, to rearrange the order of messages of the tables containing MessageA and MessageC before MessageB is finished being sent. If all tables are empty, Iguana will wait for the amount of time specified by Poll time before re-executing the queries. | ||||||||||||||||||||
 |