Creating publications

Data Replication with SQL Remote
PART 2. Replication Design for SQL Remote
CHAPTER 7. SQL Remote Design for Adaptive Server Anywhere

Creating publications

This section describes how to create simple publications consisting of whole tables, or of column-wise subsets of tables.

For Info Simple publications are also discussed in the chapter A Tutorial for Adaptive Server Anywhere Users.

Creating publications for Adaptive Server Anywhere using Sybase Central

You can add a publication to a database from within the SQL Remote folder of Sybase Central.

To create a publication from Sybase Central:

Open the SQL Remote folder for your database, which is inside the database container.
Click the Publications folder.
Double-click Add Publication. The Publication Wizard is displayed.
Follow the instructions in the Wizard.

For Info For more information on how to use Sybase Central, see the Sybase Central online Help.

With Sybase Central, you do not need to know the SQL syntax in order to create publications. The remainder of this section discusses different kinds of publication that can be created. It describes the SQL syntax needed for these publications. However, each of the publications can also be created from Sybase Central.

To send SQL statements to an Adaptive Server Anywhere database, you can use the Interactive SQL utility.

Publishing a whole table

The simplest publication you can make consists of a single article, which consists of a single, entire, table.

To create a publication that includes all the rows and columns of a single table:

Execute a create publication statement specifying the table you wish to publish. The syntax is as follows:
```
CREATE PUBLICATION publication_name (
   TABLE table_name
)
```

Example

The following statement creates a publication that publishes the whole customer table:
```
CREATE PUBLICATION pub_customer (
   TABLE customer
)
```

Publishing some of the columns in a table

Partitioning tables

An article that contains only some of the columns of a table can be called a column-wise partitioning of the table. An article that contains only some of the rows of a table can be called a row-wise partitioning of the table. Articles can be both column-wise and row-wise partitions of a table.

Only column-wise partitioning is described in this section. For information on row-wise partitioning of tables, see Publishing some of the rows from a table.

You create a publication that contains all the rows, but only some of the columns, of a table by specifying a list of columns in the CREATE PUBLICATION statement.

For Info Any article must conform to the requirements listed in Conditions for valid articles.

To create a publication that includes all the rows and some of the columns of a table:

Enter a CREATE PUBLICATION statement that includes the column names you wish to include. The syntax is as follows:
```
CREATE PUBLICATION publication_name (
   TABLE table_name ( column_name, ... )
)
```

Example

The following statement creates a publication that publishes all rows of the id, company_name, and city columns of the customer table:
```
CREATE PUBLICATION pub_customer (
   TABLE customer ( 
      id,
      company_name,
      city )
)
```

Publishing a set of tables

When publishing a set of tables, a separate article is required for each table in the publication. The following statement creates a publication including all columns and rows in each of a set of tables from the Adaptive Server Anywhere 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 to avoid referential integrity errors.
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.

Publishing some of the rows from a table

There are two ways of including only some of the rows in a publication:

WHERE clause You can 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.
Subscription expression You can use a subscription expression to include a different set of rows in different subscriptions to publications containing the article.

You can combine a WHERE clause and a subscription expression in an article.

When to use WHERE and subscription expressions

You should use a Subscription expression when different subscribers to a publication are to receive different rows from a table. The Subscription expression is the most powerful method of partitioning tables.

The WHERE clause is used to exclude a set of rows from all subscriptions to a publication.

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.

To create a publication using a WHERE clause:

Enter a CREATE PUBLICATION statement that includes the rows you wish to include in a WHERE clause. The syntax is as follows:
```
CREATE PUBLICATION publication_name (
   TABLE table_name ( column_name, ... )
      WHERE search-condition
)
```

Example

The following statement creates a publication that publishes the id, company_name, city, and state columns of the customer table, for the customers marked as active in the status column.
```
CREATE PUBLICATION pub_customer (
   TABLE customer ( 
      id,
      company_name,
      city,
      state )
   WHERE status = 'active'
)
```
In this case, the status column is not included in the publication. It must therefore have a default value so that inserts at remote databases will not fail at the consolidated database.

Publishing a subset of rows using a subscription expression

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 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 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:

Enter a CREATE PUBLICATION statement that includes the expression you wish to use as a match in the subscription expression. The syntax is as follows:
```
CREATE PUBLICATION publication_name (
   TABLE table_name ( column_name, ... )
      SUBSCRIBE BY expression
)
```

Example

The following statement creates a publication that publishes the id, company_name, city, and state columns of the customer table, and which matches the rows with subscribers according to the value of the state column:
```
CREATE PUBLICATION pub_customer (
   TABLE customer ( 
      id,
      company_name,
      city,
      state )
   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 pub_customer ('GA')
FOR Ann_Taylor ;
CREATE SUBSCRIPTION
TO pub_customer ('MA')
FOR Sam_Singer
```

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 Partitioning tables that do not contain the subscription expression.
For more information on creating subscriptions, see Creating subscriptions.
In Sybase Central, the Publication Wizard guides you through creating a subscription 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 subscription expression to partition rows by subscription.
DBA authority is required to create publications.
Publications can be altered and dropped only by the DBA.
Altering publications in a running SQL Remote setup is likely to cause replication errors, and could lead to loss of data in the replication system unless carried out with care.
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 .
For other considerations of referential integrity, see the section Designing to avoid referential integrity errors.