Show TOC

create publicationLocate this document in the navigation structure

Creates a publication for tables or stored procedures that are to be replicated as a group to one or more subscribing replicate databases.

Syntax
create publication <pub_name
	>with primary at <data_server>.<database>
Parameters
pub_name

A name for the publication. It must conform to the rules for identifiers and be unique for the specified primary data server and database.

with primary at data_server.database

Specifies the location of the primary data. If the primary database is part of a warm standby application, <data_server.database> is the name of the logical data server and database.

Examples
Example 1

Creates a publication called <pubs2_pub> that you can use to replicate data for multiple tables and stored procedures in the <pubs2> database.

create publication pubs2_pub
 with primary at TOKYO_DS.pubs2
Usage
  • Use create publication to create a publication, an object that makes it easy to set up replication for multiple tables or stored procedures in a database. You create a publication, add articles, which specify replication definitions, and then create a single subscription for the publication.

  • Execute create publication at the Replication Server that manages the database where the primary data is stored.

  • For more information about working with replication definitions, articles, and publications, see the Replication Server Administration Guide Volume 1.

    For more information about subscribing to publications, see Replication Server Administration Guide Volume 1 > Managing Subscriptions.

  • Replication Server distributes information about a new publication to a replicate site only when you create or refresh a subscription for the publication.

Requirements for Using create publication

  • Before executing create publication, make sure that:
    • The publication name you enter is unique for the primary data server and database.

    • A connection exists from the Replication Server to the database where the primary tables or stored procedures are stored.

Preparing Publications for Subscription

  • After you create a publication, you use create article to create articles and assign them to the publication. An article specifies a table replication definition or function replication definition and includes optional where clauses according to the needs of the subscribing replicate site. See create article for more information.

  • Because a replicate table cannot subscribe to two or more replication definitions for the same primary object, a publication cannot contain two or more articles for different replication definitions for the same primary table and the same replicate table.

  • When all of the articles have been assigned, you must validate the publication using validate publication before a replicate site can subscribe to it. Validating a publication verifies that the publication contains at least one article and marks the publication ready for subscription. See validate publication for more information.

  • To check publication status, use check publication. This command displays the number of articles the publication contains and indicates if the publication is valid. See check publication for more information.

Subscribing to Publications

  • When a publication is valid, you can create a subscription for the publication in order to begin replication to a replicate database. All forms of subscription materialization are supported. See create subscription or define subscription for more information.

  • When you create a publication subscription, Replication Server creates a separate underlying subscription for each article that the publication contains. Each article subscription uses the name of the parent publication subscription.

  • A subscription to a publication cannot include a where clause. Instead, you can customize replication to replicate sites by including one or more where clauses in each article the publication contains.

Articles for Table Replication Definitions

  • If a publication contains articles for table replication definitions only, you can use create subscription to subscribe to the publication using atomic or non-atomic materialization. See create subscription for more information.

  • You can also use bulk materialization for the publication subscription:
    • When data already exists at the replicate database, use create subscription with the without materialization clause.

    • When you must manually transfer subscription data, use define subscription and the other bulk materialization commands. See define subscription for more information.

Articles for Function Replication Definitions

  • If a publication contains articles for function replication definitions only, use bulk materialization for the publication subscription:
    • When data already exists at the replicate database, use create subscription with the without materialization clause. See create subscription for more information.

    • When you must manually transfer subscription data, use define subscription, activate subscription, and validate subscription to subscribe to the publication using bulk materialization. See define subscription for more information.

Articles for Both Table and Function Replication Definitions

  • If a publication contains articles for both table replication definitions and function replication definitions, you can use the same subscription command even though each type of replication definition requires a different materialization method.

    In order to create the subscription, first transfer data to the replicate database for component subscriptions that require bulk materialization, such as those for function replication definitions. Then use create subscription to subscribe to the publication:
    • Subscriptions for articles for table replication definitions are materialized using atomic or non-atomic materialization—unless you use the without materialization clause.

    • Subscriptions for articles for function replication definitions are materialized without materialization.

      In cases where the stored procedure for a function replication definition operates on a table for which there is also a table replication definition, no separate data transfer is necessary.

Refreshing Publication Subscriptions

  • If you add a new article to an existing publication, or drop an article from the publication, the publication is invalidated. Although replication for existing articles continues unaffected, in order to begin replication for any new articles or create new publication subscriptions you must:
    • Validate the publication when you have completed making changes to the publication, then

    • Refresh the publication subscription.

  • In order to refresh a publication subscription for atomic or non-atomic materialization:
    • Re-create the subscription using create subscription. See create subscription for more information.

  • In order to refresh a publication subscription for bulk materialization:
    • When data already exists at the replicate database, use create subscription with the without materialization clause.

    • Re-create the subscription using define subscription, activate subscription, and validate subscription and transfer subscription data manually as necessary. See define subscription for more information.

Dropping Subscriptions, Articles, and Publications

  • You can drop a subscription to a publication and, optionally, purge the subscription data for the component subscriptions to articles for table replication definitions. See drop subscription.

  • If there is no subscription, you can drop an article that a publication contains and, optionally, drop the associated replication definition if it is not used elsewhere. After you drop an article, the publication is invalid. See drop article.

  • You can drop a publication if there are no subscriptions for the publication. When you drop a publication, its articles are also dropped. Optionally, you can also drop all of the replication definitions for the publication’s articles, if they are not used elsewhere. See drop publication for more information.

Publications in Warm Standby Applications

  • In a warm standby application, replication definitions used in replicating to the standby database may also be specified by articles included in publications.

Permissions

create publication requires “create object” permission.