Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Remote » Replication Design for SQL Remote » SQL Remote installation design » Sharing rows among several subscriptions » The Policy example

 

The publication

A single publication provides the basis for the data sharing:

CREATE PUBLICATION SalesRepData (
   TABLE SalesReps,
   TABLE Policy SUBSCRIBE BY rep_key,
   TABLE Customers SUBSCRIBE BY (
      SELECT rep_key FROM Policy
      WHERE Policy.cust_key =
         Customers.cust_key
   )
);

The subscription statements are exactly as in the previous example.

How the publication works

The publication includes part or all of each of the three tables. To understand how the publication works, it helps to look at each article in turn:

  • SalesReps table   There are no qualifiers to this article, so the entire SalesReps table is included in the publication.
    ...
       TABLE SalesReps,
    ...

  • Policy table   This article uses a subscription expression to specify a column used to partition the data among the sales reps:
    ...
       TABLE Policy
       SUBSCRIBE BY rep_key,
    ...

    The subscription expression ensures that each sales rep receives only those rows of the table for which the value of the rep_key column matches the value provided in the subscription.

    The Policy table partitioning is disjoint: there are no rows that are shared with more than one subscriber.

    • Customers table   A subscription expression with a subquery is used to define the partition. The article is defined as follows:
      ...
         TABLE Customers SUBSCRIBE BY (
            SELECT rep_key
            FROM Policy
            WHERE Policy.cust_key =
               Customers.cust_key
         ),
      ...

      The Customers partitioning is non-disjoint: some rows are shared with more than one subscriber.

Multiple-valued subqueries in publications

The subquery in the Customers article returns a single column (rep_key) in its result set, but may return multiple rows, corresponding to all those sales representatives that deal with the particular customer. When a subscription expression has multiple values, the row is replicated to all subscribers whose subscription matches any of the values. It is this ability to have multiple-valued subscription expressions that allows non-disjoint partitioning of a table.