SQL/MX Queuing and Publish/Subscribe Services

Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
B-11
Using a Holdable Cursor to Efficiently Increase
Concurrency
Dequeueing Parcel Notifications From a Single Partition
The router can receive and remove entries in the inbox table by partition by the
scanner by issuing this SELECT statement for a cursor specification:
/* Declare cursor */
EXEC SQL DECLARE get_arrivals CURSOR WITH HOLD FOR
SELECT * FROM (DELETE FROM STREAM(inbox)
WHERE destination BETWEEN :start AND :end
FOR SKIP CONFLICT ACCESS)) AS inbox ;
...
start = 229; end = 260;
...
EXEC SQL OPEN get_arrivals;
...
/* Fetch arrivals */
EXEC SQL FETCH get_arrivals
INTO :destination,:origin,:label,:priority;
The values for the start and end host variables determine which partition of the
inbox queue is accessed by the application. To use this method, the start and end
values must occur within the range of the destination values for a single partition.
Using a Holdable Cursor to Efficiently Increase Concurrency
The router can commit its transactions without closing and reopening its cursor
because its cursor was declared WITH HOLD. Committing the transaction enables the
deletes of the parcel notifications in the inbox to be durable and allows other
applications access to key values that were locked during the router's transaction.
Because the cursor was declared WITH HOLD, it is not necessary for the router to
reopen its position after this next transaction commits, which allows the SQL executor
to operate efficiently.
/* Declare cursor */
EXEC SQL DECLARE get_arrivals CURSOR WITH HOLD FOR
SELECT origin, destination, label, priority
FROM (SELECT * FROM (DELETE FROM STREAM(inbox)
WHERE destination BETWEEN :start AND :end
FOR SKIP CONFLICT ACCESS)) AS inbox;
...
start = 229; end = 260;
...
EXEC SQL BEGIN WORK;
EXEC SQL OPEN get_arrivals;
...
/* loop forever */
for (;;) {
/* Fetch arrivals */
EXEC SQL FETCH get arrivals
INTO :origin, :destination, :label, :priority;
...
/* Route package to intermediate hub */
routePackage(origin, destination, label, priority);