Special considerations

Binding a table to a queue creates a useful interface between the queue and the database. However, due to the inherent limitations of a queue, not all database functionality can be used.

When a message is fetched from a queue, the default database processing is to dequeue, or remove, it. Every time a queue is read by the database, the data within the queue changes. This behavior differs from a standard read by a database, in which the data does not change. Supplying only a mapping that enables users to browse, where reading does not remove the queue, eliminates a major queue functionality. Enabling both processing models provides more options and requires corresponding responsibility.

By default, the top element is removed when a message is fetched from a queue. WMQ allows messages to be retrieved based upon a correlid. A correlid is a correlation identifier that can be used as a key, for example, to correlate a response message to a request message. If the correlid of the message matches the correlid of a request, the message is returned. If the VTI table is qualified with the correlid column, the correlid qualifier is passed into the WMQ request to fetch a value.

In the following example, a queue has three messages and only the second message contains a correlid, which is named 'fred'. The following statement removes all three messages from the queue and places them in a table named flounder: 
INSERT into flounder (deQueuedMsg) values (SELECT msg from vtimq);

When execution completes, no messages remain on the queue and three new rows appear in the flounder table.

The following example qualifies the vtimq table: 
INSERT into flounder (deQueuedMsg) values (SELECT msg from vtimq where
correlid = 'fred');
The above statement creates two groups of messages:
  • Messages that failed the correlid = 'fred' qualification
  • Messages that passed the correlid = 'fred' qualification. The one message that passed the qualification is located in the flounder table.

Statements including qualifiers other than equality (=) or NULL return an error. Statements including NULL return unexpected results.