Subscribe Services

Examples

HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002

B-9

Joining Invoices With Contracts

The application can retrieve contract data along with data from a particular invoice by

issuing this SELECT statement:

SELECT invoices.amount, contracts.amount, invoices.contractnbr

FROM (DELETE FROM STREAM(invoices)) as invoices

LEFT JOIN contracts

ON invoices.contractnbr = contracts.contractnbr;

For this example, the join uses the primary key of the contracts table to match invoices

to their respective contract.

Left Join

For this example, if you use a left join, invoices with no matching contract are

dequeued and returned to the application so that corrective action can be initiated.

Otherwise, if you use an inner join, invoices are dequeued but are not returned to the

application when there is no matching contract.

Materialize

Depending on the size of the inner table, the amount of memory available, and many

other factors, the SQL compiler might choose a plan for the join that materializes, or

caches, the inner table. This choice can affect the behavior of a stream joined with a

table.

Suppose that, while the previous SELECT statement was waiting for new invoices from

the invoices stream, a new contract and a matching invoice were published into the

database. The SELECT statement would read the new invoice from the stream, but if

the contract table were materialized, the new matching contract would not be available

to be joined with the invoice.

To prevent materialization of the inner table of a join with a stream, thereby allowing

even new rows in the inner table to be available for matching with new rows from the

stream, issue this statement before compiling the SELECT statement:

CONTROL QUERY DEFAULT MATERIALIZE 'off';

In some applications, it might not be necessary to consider new rows appearing in the

inner table to be joined with a stream. In these cases, to allow the SQL compiler to

produce its most efficient plan, leave this compiler directive set to the system value.

Scenario 5. The Shipping Application

A shipping company tracks and routes incoming parcels. A scanner process scans

each incoming parcel and publishes a notification to an inbox, implemented as an SQL

table. A router process subscribes to the inbox and, when notified, sends the parcel to

its destination and removes the notification from the inbox.