Publishing only some rows using a subscription expression

You can specify a subscription expression to include a different set of rows in different subscriptions to publications containing the article.

For example, in a mobile workforce situation, a sales publication may be wanted where each sales rep subscribes to their own sales orders, enabling them to update their sales orders locally and replicate the sales to the consolidated database.

Using the WHERE clause model, a separate publication for each sales rep would be needed: the following publication is for sales rep Samuel Singer: each of the other sales reps would need a similar publication.

CREATE PUBLICATION PubOrdersSamuelSinger (
   TABLE SalesOrders
      WHERE SalesRepresentative = 856
);

To address the needs of setups requiring large numbers of different subscriptions, SQL Remote allows a subscription expression to be associated with an article. Subscriptions receive rows depending on the value of a supplied expression.

Benefits of subscription expressions

Publications using a subscription expression are more compact, easier to understand, and provide better performance than maintaining several WHERE clause publications. The database server must add information to the transaction log, and scan the transaction log to send messages, in direct proportion to the number of publications. The subscription expression allows many different subscriptions to be associated with a single publication, whereas the WHERE clause does not.

To create an article using a subscription expression (Sybase Central)

Connect to the database as a user with DBA authority.
In the left pane, select the Publications folder.
From the File menu, choose New » Publication.

The Create Publication Wizard appears.
Type a name for the publication and click Next.
On the Specify SUBSCRIBE BY Restrictions page, enter the subscription expression.
Follow the remaining instructions in the wizard.

To create an article using a subscription expression (SQL)

Connect to the database as a user with DBA authority.
Execute a CREATE PUBLICATION statement that includes the expression you want to use as a match in the subscription expression.

Examples

The following statement creates a publication that publishes the ID, CompanyName, City, State, and Country columns of the Customers table, and which matches the rows with subscribers according to the value of the State column:

CREATE PUBLICATION PubCustomers (
   TABLE Customers (
      ID,
      CompanyName,
      City,
      State,
      Country )
   SUBSCRIBE BY State
);

The following statements subscribe two employees to the publication: Ann Taylor receives the customers in Georgia (GA), and Sam Singer receives the customers in Massachusetts (MA).

CREATE SUBSCRIPTION
TO PubCustomers ('GA')
FOR Ann_Taylor ;
CREATE SUBSCRIPTION
TO PubCustomers ('MA')
FOR Sam_Singer;

Users can subscribe to more than one publication, and can have more than one subscription to a single publication.