Subscribe Services

Major Queuing Features

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

2-17

Restarting a Subscriber

Certain problems occur that can cause the cursor to close. Subscribers must be

restarted after:

•

A stream overflow

•

A “blown-away” open caused by utility operations or DDL changes to a table

•

An event that causes the application to be taken offline

•

A transaction timeout (the TMF AUTOABORT timeout period might have elapsed)

•

Other errors

The application can react to these conditions by closing the cursor, rolling back the

current transaction, starting a new transaction, and reopening the cursor. Or perhaps

the application process can stop and restart. Two issues must be considered when

restarting the subscriber:

•

How to “catch up” with rows that were published while the subscriber was offline

•

How to avoid reading rows scanned before the subscriber went offline

These issues can be addressed with destructive or nondestructive subscribers.

Destructive Subscribers

For destructive subscribers, use an embedded DELETE to perform catch up and avoid

rereading automatically. Rows that are in the queue when the subscriber restarts will

either be rows that were inserted while the subscriber was offline or rows that the

subscriber did not scan before being taken offline.

Use an embedded UPDATE to update a column to show that the row is processed.

The same column can appear in the embedded UPDATE statement’s selection

predicate to eliminate rows that have already been processed. This allows the

subscriber to catch up and to avoid rereading rows.

Nondestructive Subscribers

For nondestructive subscribers, use the AFTER LAST ROW clause to cause the cursor

to skip the initial scan on the table and read only rows that are published after the

cursor is opened. For example:

SELECT * FROM

STREAM(invoices) AFTER LAST ROW;

Assuming the subscriber has already read existing rows in the table, the use of AFTER

LAST ROW ensures that rows will not be reread.

The AFTER LAST ROW clause does not address the issue of catching up with rows

that were published while the subscriber was offline. In some applications (for

example, a stock quotes monitor or a real-time fraud detector), catching up might not

be required. Other applications can be designed and operated to prevent the catch-up

problem. Operators can stop publishers first and then stop subscribers after they have