SQL/MX Queuing and Publish/Subscribe Services

Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
B-7
Dequeueing Invoices by Priority
After the first regular scan of the table has been completed, an application might
receive invoices out of order—that is, with a higher priority. Invoices might come back
out of order because:
•
The row was initially skipped due to SKIP CONFLICT ACCESS (that is, it was
locked during the regular scan on the table and then unlocked later)
•
A row did not exist in the table when the regular scan was performed and was
inserted into the table later.
Invoices are inserted randomly into the table, and a new invoice of higher priority could
be submitted after already receiving and processing an invoice of lower priority.
Using a Cursor to Retrieve in Priority Order
An embedded SQL application can dequeue invoices in the invoice table in priority
order by issuing these statements:
/* Declare cursor */
EXEC SQL DECLARE get_invoices CURSOR WITH HOLD FOR
SELECT contractnbr, amount FROM
(DELETE FROM STREAM(invoices)
FOR SKIP CONFLICT ACCESS) AS invoices
ORDER BY priority DESC;
...
EXEC SQL OPEN get_invoices;
...
/* Fetch invoices */
EXEC SQL FETCH get_invoices
INTO :contractnbr, :amount;
However, new entries are not necessarily dequeued in priority order, either because
they were skipped due to the use of SKIP CONFLICT ACCESS, or because the new
entry might have been published after entries of a lower priority have already been
fetched. When retaining an open cursor across fetch operations, SQL/MX attempts to
return entries in priority order first but does not reposition the cursor on each fetch.
When you use a cursor, the ORDER BY requirement is interpreted by SQL/MX as being
a best-effort directive.
Using SELECT INTO to Retrieve in Priority Order
An application can use a SELECT INTO statement (and no cursor) to ensure
dequeueing the entry with the highest priority each time the application accesses the
invoice queue:
EXEC SQL SELECT contractnbr, amount FROM
(DELETE FROM STREAM(invoices)
FOR SKIP CONFLICT ACCESS) AS invoices
ORDER BY priority DESC
INTO :contractnbr, :amount;
The SELECT INTO statement guarantees that each retrieval operation is positioned at
the beginning of the clustering key range for the available entries in the queue.