Creating publications

Data Replication with SQL Remote
PART 2. Replication Design for SQL Remote
CHAPTER 8. SQL Remote Design for Adaptive Server Enterprise

Creating publications

In this section

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 Enterprise Users.

Creating publications for Adaptive Server Enterprise using Sybase Central

In Sybase Central, you can add a publication to a database from within the SQL Remote folder. The SQL Remote folder is displayed inside a database container.

To create a publication from Sybase Central:

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

For Info For more information, 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.

Creating whole-table articles

The simplest type of article is one that includes all the rows and columns of a database table.

To create an article that includes all the rows and columns of a table:

Mark the table for replication. You do this by executing the sp_add_remote_table procedure:
```
sp_add_remote_table table-name
```
Add the table to the publication. You do this by executing the sp_add_article procedure:
```
sp_add_article publication-name, table-name
```

Example

The following commands add the table SalesRep to the SalesRepData publication:

sp_add_remote_table 'SalesRep'
sp_add_article 'SalesRepData', 'SalesRep'
go

Creating articles containing some of the columns in a table

To create an article that includes only some of the columns from a table, you need to list the columns that you wish to include, using sp_add_article_col. If no columns are listed, the article includes all columns of the table.

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

Mark the table for replication. You do this by executing the sp_add_remote_table procedure:
```
sp_add_remote_table table-name
go
```
Add the table to the publication. You do this by executing the sp_add_article procedure:
```
sp_add_article publication-name, table-name
go
```
The sp_add_article procedure adds a table to a publication. By default, all columns of the table are added to the publication. If you wish to add only some of the columns, you must use the sp_add_article_col procedure to specify which columns you wish to include.
Add individual columns to the publication. You do this by executing the sp_add_article_col procedure for each column:
```
sp_add_article_col publication-name, 
   table-name, 
   column-name
go
```

Example

The following commands add only the rep_key column of the table SalesRep to the SalesRepData publication:

sp_add_remote_table 'SalesRep'
sp_add_article 'SalesRepData',
   'SalesRep'
sp_add_article_col 'SalesRepData', 
   'SalesRep',
   'rep_key'
go

Creating articles containing some of the rows in a table

There are two ways of including only some of the rows from a table in an article:

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

Allowed clauses

In SQL Remote for Adaptive Server Enterprise, the following limitations apply to each of these cases:

WHERE clause limitations The only form of WHERE clause supported is the following:
```
WHERE column-name IS NOT NULL.
```
Subscription column SQL Remote for Adaptive Server Anywhere supports expressions other than column names. For Adaptive Server Enterprise, the subscription expression must be a column name.

When to use WHERE and SUBSCRIBE BY

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.

Creating an article using a WHERE clause

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

To create an article using a WHERE clause:

If you have not already done so, mark the table for replication. You do this by executing the sp_add_remote_table procedure:
```
sp_add_remote_table table_name
```
Add the table to the publication. You do this by executing the sp_add_article procedure: Specify the column name corresponding to the WHERE column IS NOT NULL clause in the third argument to the procedure:
```
sp_add_article publication_name, 
   table_name,
   column_name
```
Do not specify IS NOT NULL; it is implicit. Specify the column name only.
If you wish to include only a subset of the columns in the table, specify the columns using the sp_add_article_col procedure. You must include the column specified in your WHERE clause in the article.

Example

The following set of statements create a publication containing a single article, which includes only those rows of test_table for which column col_1 is not null:
```
sp_create_publication test_pub
sp_add_remote_table test_table
sp_add_article test_pub, test_table, col_1
go
```

Creating an article using a subscription column

The subscription column is used when rows are to be shared among many remote databases.

To create an article using a subscription column:

If you have not already done so, mark the table for replication. You do this by executing the sp_add_remote_table procedure:

sp_add_remote_table table_name
Add the table to the publication. You do this by executing the sp_add_article procedure: Specify the column name you wish to use as a subscription expression in the fourth argument to the procedure:

sp_add_article publication_name, table_name, NULL, column_name

You must include the NULL entry to avoid adding a WHERE clause.
If you wish to include only a subset of the columns in the table, specify the columns using the sp_add_article_col procedure. You must include the column specified in your subscription expression in the article.

Example

The following set of statements create a publication containing a single article, which supports subscriptions based on the value of column col_1:
```
sp_create_publication test_pub
sp_add_remote_table test_table
sp_add_article test_pub, 
   test_table, 
   NULL, 
   col_1
go
```

Notes on articles

You can combine a WHERE clause and a subscription expression in an article.
All columns in the primary key must be included in any article.
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.