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

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 10 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


The without purge clause


The with purge clause

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

check subscription, create subscription, define subscription, drop article, drop function replication definition,drop publication, drop replication definition, resume connection, rs_select, rs_select_with_lock