SQL/MX Queuing and Publish/Subscribe Services

Major Queuing Features
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
2-12
Rowset Integration
Applications that delete or update many rows, using embedded DELETEs and
UPDATEs, experience a significant performance boost if they use a holdable cursor
and are coded to avoid closing and reopening the cursor with each COMMIT WORK
statement.
Rowset Integration
The integration of rowsets into the queuing and publish/subscribe features of SQL/MX
enables applications to enqueue or dequeue multiple rows at one time instead of
having to enqueue or dequeue rows one at a time.
For example, this excerpt specifies the target host variables as rowset arrays:
EXEC SQL BEGIN DECLARE SECTION;
ROWSET[10] int arrayInv_amt;
ROWSET[10] int arrayInv_nbr;
long numrows;
short i;
...
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE get_invoices CURSOR WITH HOLD FOR
SELECT amount, contractnbr
FROM
(DELETE FROM STREAM(invoices)
FOR SKIP CONFLICT ACCESS) AS invoices;
EXEC SQL BEGIN WORK; /* Start transaction */
EXEC SQL OPEN get_invoices; /* Open cursor */
/* Wait for newly arrived invoices */
while (1) {
/* Dequeue invoices into rowset */
EXEC SQL FETCH get invoices
INTO :arrayInv_amt, :arrayInv_nbr;
EXEC SQL GET DIAGNOSTICS :numrows = ROW_COUNT;
for (i = 0; i < numrows; i++) {
... /* Process row in some way */
}
EXEC SQL COMMIT WORK; /* Commit transaction */
EXEC SQL BEGIN WORK; /* Start new transaction */
}
EXEC SQL COMMIT WORK; /* Commit final transaction */
...
The rowsets are retrieved ten (or fewer than ten) at a time until the application must
wait for new arrivals.
Note. Rowset integration is not supported in Java applications that contain embedded
SQL/MX statements.
Note. Use rowsets with a cursor as documented under Rowset Integration
. The cursor fetch
into a rowset from a stream waits until the rowset is filled.