SQL/MX Queuing and Publish/Subscribe Services

Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
B-10
The Inbox Table
The sample database for this scenario is provided in Appendix A, The Shipping
Database.
The Inbox Table
Suppose that the inbox table is defined as:
CREATE TABLE psdb.pubs.inbox
(destination INT NOT NULL,
origin INT NOT NULL,
label INT,
priority INT,
PRIMARY KEY (destination, origin)
)
PARTITION (ADD FIRST KEY 230 LOCATION $DATA05,
ADD FIRST KEY 260 LOCATION $DATA06
);
The destination column identifies the physical destination. The origin column identifies
the physical scanner. The label and priority columns are read from the bar code label
of the incoming parcel. The clustering key is determined by the destination and origin.
The inbox table is partitioned by destination to support scalability.
Inserting Notifications of Incoming Parcels
A scanner operates on the shipping database. A scanner inserts notifications of
incoming parcels into the inbox. The notifications are routed in a transparent manner to
the respective table partition, depending on the value of the destination column.
Eventually, table partitions might need to be added or removed. Repartitioning can be
done online. It is transparent to the scanner application and does not interrupt the
scanner process.
Subscribing to Route Incoming Parcels
A router operates on the shipping database. Only a single router accesses notifications
of packages going to one particular destination. The shipping application can assign a
router to an entire physical table partition, or the shipping application can assign a
router to a logical partition.
A router subscribes to notifications in either a physical table partition or a logical
partition by issuing a SELECT statement using an embedded DELETE and stream
access mode. When subscribing to a physical partition, the router specifies a WHERE
clause for a partition’s range of destination values.
If the inbox table is repartitioned and the routers are assigned to physical partitions, it
is necessary to make some adjustments to the routers. If you add partitions, you must
add new routers. If you remove partitions, you must stop routers. In either case,
partition boundaries will probably be changed, and if so, you must close each router's
cursor, update the hostvars to reflect the new partition values, and then reopen the
cursors.