SQL/MX Queuing and Publish/Subscribe Services

Major Queuing Features
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
2-5
Embedded UPDATE
If you initiate the transaction explicitly by using BEGIN WORK, after the stream times
out, the transaction is still active, and you can either commit or abort:
CONTROL QUERY DEFAULT stream_timeout '300';
BEGIN WORK;
SELECT * FROM
(DELETE FROM STREAM(invoices)) AS invoices;
CONTRACTNBR AMOUNT PRIORITY
----------- ----------- -----------
100 10500 1
200 20390 2
300 30800 3
*** ERROR[8006] The stream timed out,
but the cursor is still open.
ROLLBACK WORK;
SELECT * FROM invoices;
CONTRACTNBR AMOUNT PRIORITY
----------- ----------- -----------
100 10500 1
200 20390 2
300 30800 3
--- 3 row(s) selected.
Whether you commit or abort, for embedded SQL statements, if you are using a
holdable cursor for the dequeue operation, you can fetch more enqueued rows after
the transaction ends. For an example of using an embedded DELETE operation for the
cursor specification in an application, see Embedded DELETE on page 3-3.
When a fetch on a stream waits longer than the duration of TMF’s autoabort timeout,
whose default value is two hours, the statement receives error 78 from the Distribution
Service. You can configure the autoabort timeout duration through TMFCOM with the
ALTER BEGINTRANS command. See the TMF Introduction for details.
Embedded UPDATE
Embedded UPDATE statements enable applications to read and update rows with a
single operation. In this example, dequeue operations are implemented by using an
embedded UPDATE of an archive flag together with stream access.