User's Guide

Part III. Using SQL Anywhere

Chapter 28. SQL Remote Administration

The publication is the key design element of a SQL Remote data replication. A publication consists of a set of articles. Each article is a selection of rows and columns from a table in the database. Rows can be selected from a table in two ways; a WHERE clause can be included in an article definition, and/or a SUBSCRIBE BY expression can be supplied (usually a column name). In the latter case, rows are sent to subscribers according to a matching value supplied in their subscription.

This section describes the mechanics of setting up publications using Sybase Central. It also describes options open to you in designing publications, governed by the CREATE PUBLICATION statement that is executed by Sybase Central. Careful publication design is crucial to a properly functioning SQL Remote setup.

  For information on designing publications, see "Designing publications".

Publishing a set of tables

A simple publication consists of a set of tables, including all columns and rows in each of the tables. The following statement creates such a publication from the sample database:

     CREATE PUBLICATION sales (
         TABLE customer,
         TABLE sales_order,
         TABLE sales_order_items,
         TABLE product
     )

Not all the tables in the database have been published. For example, subscribers receive the sales_order table, but not the employee table.

Notes

• In the sample database, the sales_order and employee tables are related by a foreign key (sales_rep in the sales_order table is a foreign key to the emp_id column in the employee table). Although this could lead to referential integrity problems in the remote database, they are easily avoided by using the database extraction utility: for a discussion of this and other publication design issues, see "Designing publications".
• A slightly different publication design (including an article that contains enough of the employee table to satisfy the foreign key relationship) would make the publication more robust; for this section we are publishing whole tables only.

In Sybase Central, you can add a publication to a database from within the SQL Remote folder.

1. Click the Publications folder, which is inside the SQL Remote folder.
2. Double-click Add Publication. The Publication Wizard is displayed.
3. Follow the instructions in the Wizard.

For more information, see the Sybase Central online Help.

Publishing a subset of columns

The product table keeps information such as unit price and current inventory for a set of products. One column lists the current inventory for each item. In the following single-table publication, this column is not replicated:

     CREATE PUBLICATION product (
         TABLE product (    id,
                             name,
                             description,
                             size,
                             color,
                             unit_price
                             )
     )

In a multi-table publication, any or all of the tables may have a subset of columns published.

In Sybase Central, the Publication Wizard guides you through selecting a subset of columns in an article.

Notes

• All columns in the primary key must be included in the publication.
• All columns used in WHERE clauses or SUBSCRIBE BY expressions must be included in the publication.
• You must not include a subset of columns in an article unless either:
• The remaining columns have default values or allow NULLs.
• No inserts are carried out at remote databases. Updates would not cause problems as long as they do not change primary key values. If you include a subset of columns in an article in situations other than these, INSERT statements at the consolidated database will fail.

Publishing a subset of rows using a WHERE clause

The following is a single-article publication sending relevant order information to Samuel Singer, a sales rep:

     CREATE PUBLICATION pub_orders_samuel_singer (
         TABLE sales_order WHERE sales_rep = 856
     )

In Sybase Central, the Publication Wizard guides you through creating a WHERE clause for an article.

The WHERE clause in a CREATE PUBLICATION statement may contain a subquery, but subqueries should only be used in very specific circumstances.

  For a description of the circumstances where you can use a subquery in a publication, see "Using subqueries in publications".

Publishing a subset of rows using a SUBSCRIBE BY expression

In a typical 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 pub_orders_samuel_singer (
         TABLE sales_order WHERE sales_rep = 856
     )

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

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

The following statement creates a publication of the sales_order table with a SUBSCRIBE BY expression that is just the column sales_rep.

     CREATE PUBLICATION pub_orders (
         TABLE sales_order SUBSCRIBE BY sales_rep
     )

Each subscription to this publication specifies a value, using the syntax described in "Setting up subscriptions". The subscriber receives updates of those rows for which the SUBSCRIBE BY expression (here a column) matches the value of the subscription parameter (the sales rep's employee id). For example, the following statement creates a subscription for Samuel Singer to the pub_orders publication:

     CREATE SUBSCRIPTION
     TO pub_orders ( '856' )
     FOR SamS

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

For more information

• For information on how to use subqueries in a publication, see "Using subqueries in publications".
• For more information on creating subscriptions, see "Setting up subscriptions".
• In Sybase Central, the Publication Wizard guides you through creating a SUBSCRIBE BY expression for an article.

Dropping publications

Publications can be dropped using the DROP PUBLICATION statement. The following statement drops the publication named pub_orders.

     DROP PUBLICATION pub_orders

Dropping a publication has the side effect of dropping all subscriptions to that publication.

Notes on publications

• The different publication types described above can be combined. A single publication can publish a subset of columns from a set of tables, and use both a WHERE clause to select a set of rows to be replicated and a SUBSCRIBE BY expression to partition rows by subscription.
• DBA authority is required to create publications.
• Publications can be altered and dropped by the DBA.
• Altering publications in a running SQL Remote setup is likely to cause replication errors, and could lead to loss of data.
• Views cannot be included in publications.
• Stored procedures cannot be included in publications. For a discussion of how SQL Remote replicates procedures and triggers, see "Replication of procedures and triggers".
• For other considerations of referential integrity, see the section "Designing publications".