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

SQL Anywhere 11.0.1 (Français) » SQL Remote » SQL Remote Replication Design » SQL Remote replication design and set up » Publications and articles


Publish only some rows in a table

To create a publication that contains only some of the rows in a table, you must write a search condition that matches only the rows you want to publish. Use of one of the following clauses in your search condition:

  • SUBSCRIBE BY clause   Use the SUBSCRIBE BY clause when multiple subscribers to a publication receive different rows from a table.

    The SUBSCRIBE BY clause is recommended when your SQL Remote system requires a large number of subscriptions. The SUBSCRIBE BY clause allows many subscriptions to be associated with a single publication, whereas the WHERE clause does not. Subscribers receive rows depending on the value of a supplied expression.

    Publications using a SUBSCRIBE BY clause are more compact, easier to understand, and provide better performance than maintaining several WHERE clause publications.

    See Publish only some rows using the SUBSCRIBE BY clause.

  • WHERE clause   Use a WHERE clause to include a subset of rows in an article. All subscribers to the publication containing this article receive the rows that satisfy the WHERE clause.

    All unpublished rows must have a default value. Otherwise, when the remote database tries to insert new rows from the consolidated database, an error occurs.

    You can combine a WHERE clause in an article.

    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 WHERE clause does not allow many subscriptions to be associated with a single publication; however the SUBSCRIBE BY clause does.

    See Publish only some rows using a WHERE clause.


You need a publication that enables each sales representative to:

  • Subscribe to their sales orders.

  • Update their sales orders locally.

  • Replicate their sales to the consolidated database.

If you use the WHERE clause, you would need to create separate publications for each sales representative. The following publication is for a sales representative named Sam Singer; each of the other sales representatives would need a similar publication.

   TABLE SalesOrders
      WHERE Active = 1

The following statement subscribes Sam Singer to the PubsOrdersSamSinger publication.

TO PubOrdersSamSinger
FOR Sam_Singer;

If you use the SUBSCRIBE BY clause, you need only one publication. All of the sales representatives can use the following publication:

   TABLE SalesOrders 
   SUBSCRIBE BY SalesRepresentativeID

The following statement subscribes Sam Singer to the PubsOrders publication by his ID, 8887.

TO PubOrders ('8887')
FOR Sam_Singer;

Publish only some rows using the SUBSCRIBE BY clause
Publish only some rows using a WHERE clause