drop subscription

Description

Drops a subscription to a database replication definition, table replication definition, function replication definition, article, or publication.

Syntax

drop subscription sub_name
for {table_rep_def | function_rep_def |
{article article_name in pub_name |
    publication pub_name | database replication definition db_repdef 
       with primary at data_server.database}
 with replicate at data_server.database
[without purge [with suspension
     [at active replicate only]] |
   [incrementally] with purge]

Parameters

Examples

Example 1

Drops the authors_sub subscription for the authors_rep table replication definition. The replicate data is in the pubs2 database of the SYDNEY_DS data server. The rows replicated via the subscription are purged from the replicate table, where they are not part of another subscription:

drop subscription authors_sub
  for authors_rep
    with replicate at SYDNEY_DS.pubs2
    with purge

Example 2

Drops the titles_sub subscription for the titles_rep table replication definition. The replicate data is in the pubs2 database of the SYDNEY_DS data server. The rows replicated via the subscription remain in the replicate table:

drop subscription titles_sub
  for titles_rep
    with replicate at SYDNEY_DS.pubs2
    without purge

Example 3

Drops the myproc_sub subscription for the myproc_rep function replication definition. The replicate data is in the pubs2 database of the SYDNEY_DS data server. No subscription data is purged:

drop subscription myproc_sub
  for myproc_rep
    with replicate at SYDNEY_DS.pubs2

Example 4

Drops the subscription for the article titles_art that is part of the subscription pubs2_sub for the publication pubs2_pub. The primary data is in the pubs2 database of the TOKYO_DS data server and the replicate data is in the pubs2 database of the SYDNEY_DS data server. The rows that were replicated via the subscription remain in the affected replicate tables. After dropping the article subscription you can drop the article:

drop subscription pubs2_sub
  for article titles_art in pubs2_pub
    with primary at TOKYO_DS.pubs2
    with replicate at SYDNEY_DS.pubs2
    without purge

Example 5

Drops the subscription named pubs2_sub for the pubs2_pub publication, where the primary data is in the pubs2 database of the TOKYO_DS data server and the replicate data is in the pubs2 database of the SYDNEY_DS data server. The rows that were replicated via the subscription are purged from the affected replicate tables, where they are not part of another subscription:

drop subscription pubs2_sub
  for publication pubs2_pub
    with primary at TOKYO_DS.pubs2
    with replicate at SYDNEY_DS.pubs2
    with purge

Example 6

Deletes a database subscription named pubs2_sub. The without purge option ensures that Replication Server does not remove rows added by the subscription to the replicate:

drop subscription pubs2_sub
  for database replication definition pubs2_rep
    with primary at NEWYORK_DS.pubs2
    with replicate at TOKYO_DS.pubs2
    without purge

Usage

• When you drop a subscription, Replication Server stops replicating the data specified by the subscription.

• Execute drop subscription at the Replication Server where you created the subscription.

• You cannot drop a table replication definition, function replication definition, article, or publication until you have dropped all subscriptions for the object.

• Use without purge to drop a subscription to a table or database replication definition or to a publication. Replicated rows remain in the replicate tables.

• When you drop a subscription to a table replication definition or publication, you must specify either without purge or with purge.

• When you drop a subscription to a function replication definition, it is always dropped “without purge”—you do not need to specify without purge.

• When you drop a publication subscription “without purge,” all of its article subscriptions are dropped together.

• Use the with purge clause to delete the rows (in the replicate table) that were replicated by the subscription. All subscription rows are purged unless they belong to another subscription at the replicate site.

• When you use with purge, Replication Server selects, from the replicate database, the set of rows that could be deleted. It then evaluates the selected rows against other subscriptions and determines whether to delete the row. The maintenance user for the replicate database must have select permission on the table.

• Deletes using with purge occur in a single transaction performed by an rs_select_with_lock function string in the replicate database.

• Deletes using with purge and incrementally occur 1000 rows at a time. This operation is performed by an rs_select function string in the replicate database.

• When you drop a publication subscription “with purge,” its article subscriptions are dropped one at a time in the reverse order that the articles were added to the publication.

Permissions

drop subscription requires “create object” permission at the replicate site and “primary subscribe” permission at the primary Replication Server.

drop subscription ... with purge also requires that the maintenance user have select permission for the replicate table.

See also