SQL/MX Queuing and Publish/Subscribe Services

Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
B-6
Scenario 3. Invoice Processing by Priority
transaction but rather wait until other transactions have processed and committed the
invoice. This strategy ensures FIFO behavior but limits concurrent processing.
Therefore, by using read committed access instead of the skip conflict access mode,
an application can retrieve invoices in the order inserted by issuing this SELECT
statement:
SELECT * FROM
(DELETE FROM STREAM(invoices)
FOR READ COMMITTED ACCESS) AS invoices;
IUse of read committed access serializes access to the queue and allows only one
transaction at a time to dequeue invoices. An application can also use a WHERE
clause within the embedded DELETE to logically partition the invoices—for example,
by contract numbers within a range—to allow for concurrent processing but enforce
FIFO processing of invoices within a particular range.
Scenario 3. Invoice Processing by Priority
Invoices are submitted to request payment for a service. In this scenario, invoices are
assigned a priority to either expedite or slow down processing.
Dequeueing by priority can be achieved by either making the priority column a
clustering key or by creating a secondary index on the priority column. In this scenario,
a secondary index is created on the priority column.
The Invoices Table
The invoice table is defined as:
CREATE TABLE pubs.invoices
(contractnbr INT, amount INT, priority INT);
CREATE INDEX pubs.iinvoice ON pubs.invoices (priority);
The priority column is the secondary index of the invoices table.
Dequeueing Invoices by Priority
For this scenario, an application can dequeue invoices in the invoice table in priority
order by issuing this SELECT statement:
SELECT * FROM
(DELETE FROM STREAM(invoices)
FOR SKIP CONFLICT ACCESS) AS invoices
ORDER BY priority DESC;
The highest priority is defined here as the priority with the highest numeric value. The
scan of the table begins with the highest priority.