SQL/MX Queuing and Publish/Subscribe Services

Major Queuing Features
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
2-9
Set Column Values On Rollback
The stream skips all existing rows and returns rows published after the stream's cursor
was opened. Note that in the case where a publisher's transaction was in progress
when the subscriber’s statement began, not all the transaction's rows would be
returned to the subscriber. Instead only the rows inserted or updated after the
statement began executing would be returned.
The AFTER LAST ROW clause can be used wherever a stream is allowed, including
with embedded UPDATEs and DELETEs.
Set Column Values On Rollback
The set on rollback feature allows applications to update columns due to a rollback of a
delete or update operation. For example, this feature can be used by applications to
set abort counts.
Suppose that the invoices table, as described in Scenario 2. The Invoices Queue
(page B-5), contains a column for the abort count:
CREATE TABLE pubs.invoices
(contractnbr INT,
amount INT,
priority INT,
abortcount INT NOT NULL);
The abortcount column must be declared as NOT NULL and cannot be part of a
secondary index.
This SELECT statement sets the abort count on rollback:
SELECT * FROM
(DELETE FROM STREAM(invoices)
SET ON ROLLBACK abortcount = abortcount + 1
FOR SKIP CONFLICT ACCESS) AS invoices;
CONTRACTNBR AMOUNT PRIORITY ABORTCOUNT
----------- ----------- ----------- ----------
100 10500 1 0
200 20390 2 0
This application is in a wait state. Suppose that the transaction performing this
dequeue operation aborts for some reason and is unable to delete the rows from the
queue.
Another application enqueues an invoice by issuing this INSERT statement:
INSERT INTO invoices (contractnbr, amount, priority)
VALUES (300, 30800, 3);