drop subscription

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

sub_name – The name of the subscription to drop. If you are dropping a subscription for an article within a publication, specify the publication subscription name.
for table_rep_def – Specifies the name of the table replication definition the subscription is for.
for function_rep_def – Specifies the name of the function replication definition the subscription is for.
for article article_name in pub_name – Specifies the name of the article the subscription is for and the name of the publication that contains the article.
for publication pub_name – Specifies the name of the publication the subscription is for.
for database replication definition db_repdef – Specifies the name of the database replication definition the subscription is for.
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. Include this clause only for a subscription to a publication or a subscription to an article.
with replicate at data_server.database – Specifies the location of the replicate data. If the replicate database is part of a warm standby application, data_server.database is the name of the logical data server and database.
without purge – Instructs Replication Server to leave rows replicated by a subscription in the replicated copy.
A subscription to a function replication definition is always dropped without purging replicate data. For a subscription to a table replication definition or a publication, you must choose either without purge or with purge. For a subscription to a database replication definition, you must include without purge.
with suspension – Used with the without purge clause, suspends the DSI after the subscription is dropped so that you can manually delete subscription rows. If the database is part of a warm standby application, with suspension suspends the DSI threads for the active and the standby databases. Delete subscription rows from both databases.
with suspension at active replicate only – Used with the without purge clause, suspends the DSI after the subscription is dropped so that you can manually delete subscription rows. In a warm standby application, the standby DSI is not suspended. This allows Replication Server to replicate delete transactions from the active database to the standby database.
incrementally – Used with the with purge clause, specifies that deletes are made 1000 rows at a time.
with purge – Used with a table replication definition, article, or publication, instructs Replication Server to remove rows (in the replicate table) that were replicated by a subscription.
A subscription to a function replication definition is always dropped without purging replicate data. For a subscription to a table replication definition or a publication, you must choose either without purge or with purge.

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.

The without purge Clause

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.

The with purge Clause

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.