ASA SQL Reference
  SQL Statements

CREATE PUBLICATION statement

Description 

Use this statement to create a publication. In MobiLink, a publication identifies synchronized data in UltraLite or Adaptive Server Anywhere remote databases. In SQL Remote, publications identify replicated data in both consolidated and remote databases.

Syntax 

CREATE PUBLICATION [ owner.]publication-name
( TABLE article-description, ... )

owner, publication-name : identifier

article-description :
  table-name [ ( column-name, ... ) ]
[ WHERE search-condition ]
[ SUBSCRIBE BY expression ]

Parameters 

article-description    Publications are built from articles. Each article is a table or part of a table. An article may be a vertical partition of a table (a subset of the table's columns), a horizontal partition (a subset of the table's rows) or a vertical and horizontal partition.

WHERE clause    The WHERE clause is a way of defining the subset of rows of a table to be included in an article. It is useful if the same subset is to be received by all subscribers to the publication.

SUBSCRIBE BY clause    In SQL Remote, one way of defining a subset of rows of a table to be included in an article is to use a SUBSCRIBE BY clause. This clause allows many different subscribers to receive different rows from a table in a single publication definition. This clause is ignored during MobiLink synchronization.

You can combine WHERE and SUBSCRIBE BY clauses in an article definition, but the SUBSCRIBE BY clause is used only by SQL Remote.

Usage 

This statement is applicable only to MobiLink and SQL Remote.

The CREATE PUBLICATION statement creates a publication in the database. A publication can be created for another user by specifying an owner name.

In MobiLink, publications are required in Adaptive Server Anywhere remote databases, and are optional in UltraLite databases. These publications and the subscriptions to them determine which data will be uploaded to the MobiLink synchronization server. You can construct a remote database by creating publications and subscriptions directly. Alternatively, you can create publications and subscriptions in an Adaptive Server Anywhere reference database, which acts as a template for the remote databases, and then construct the remote databases using the MobiLink extraction utility.

You set options for a MobiLink publication with the ADD OPTION clause in the ALTER SYNCHRONIZATION SUBSCRIPTION statement or CREATE SYNCHRONIZATION SUBSCRIPTION statement.

In SQL Remote, publishing is a two-way operation, as data can be entered at both consolidated and remote databases. In a SQL Remote installation, any consolidated database and all remote databases must have the same publication defined. Running the SQL Remote extraction utility from a consolidated database automatically executes the correct CREATE PUBLICATION statement in the remote database.

Permissions 

Must have DBA authority. Requires exclusive access to all tables referred to in the statement.

Side effects 

Automatic commit.

See also 

ALTER PUBLICATION statement

DROP PUBLICATION statement

ALTER SYNCHRONIZATION SUBSCRIPTION statement [MobiLink]

CREATE SYNCHRONIZATION SUBSCRIPTION statement [MobiLink]

sp_create_publication procedure

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

Example 

The following statement publishes all columns and rows of two tables.

CREATE PUBLICATION pub_contact (
   TABLE contact,
   TABLE company
)

The following statement publishes only some columns of one table.

CREATE PUBLICATION pub_customer (
   TABLE customer ( id, company_name, city )
)

The following statement publishes only the active customer rows by including a WHERE clause that tests the status column of the customer table.

CREATE PUBLICATION pub_customer (
   TABLE customer ( id, company_name, city, state )
   WHERE status = 'active'
)

The following statement publishes only some rows by providing a subscribe-by value. This method can be used only with SQL Remote.

CREATE PUBLICATION pub_customer (
   TABLE customer ( id, company_name, city, state )
   SUBSCRIBE BY state
)

The subscribe-by value is used as follows when you create a SQL Remote subscription.

CREATE SUBSCRIPTION TO pub_customer ( 'NY' )
   FOR jsmith