Using the drop subscription command

Dropping a subscription causes Replication Server to stop sending changes from a primary database to a replicate database. You can use the drop subscription command to drop subscriptions for either table or function replication definitions.

Execute the drop subscription command at the replicate Replication Server. It requires create object permission at the replicate Replication Server and create object or primary subscribe permission at the primary Replication Server.

Here is the syntax:

drop subscription subscription
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.db }
with replicate at data_server.database
[without purge 
[with suspension [at active replicate only ]] |
[incrementally] with purge]

If you choose the without purge dematerialization method, Replication Server does not delete subscription data from the replicate database.

If you choose the with purge dematerialization method, Replication Server logs in to the replicate database and selects data from it. If this data does not belong to any other subscriptions, the subscription data is deleted from the replicate database.

When you drop subscriptions to table replication definitions, you can purge subscription rows regardless of the materialization method you used when you created the subscription. Rows are removed only if they do not match another subscription.

You can use the check subscription command to view the progress of the drop subscription command. When the subscription status no longer exists at the primary and replicate Replication Servers, the command is complete.

Subscriptions to function replication definitions are always dropped without purging the replicate data associated with the function. You do not need to specify the without purge option.

When you are dropping subscriptions to table replication definitions, you have two basic methods to choose from. Because each method carries important implications, Replication Server requires that you explicitly choose one of these two methods:

• with purge – Replication Server removes, or dematerializes, the subscription’s rows from the replicate database, if they do not belong in other remaining subscriptions. The Replication Server logs in as the maintenance user to perform the select operation. Use the incrementally option to specify that dematerialization occurs in 1000-row increments of deletes per transaction.

• without purge – the subscription’s rows remain at the replicate database. The with suspension option leaves the connection to the replicate database suspended when drop subscription has completed, so that you can manually remove the rows.

For warm standby applications, the option with suspension at active replicate only suspends the active replicate database but not the standby replicate database.

To drop a subscription with purge, use a command like this:

drop subscription publishers_sub
for publishers_rep
with replicate at SYDNEY_DS.pubs2
with purge

To drop a subscription without purge, use a command like this:

drop subscription publishers_sub
for publishers_rep
	with replicate at SYDNEY_DS.pubs2
without purge

To drop a subscription without purge and also suspend the DSI for the replicate database so that you can manually delete the rows for the subscription, use a command like this:

drop subscription publishers_sub
for publishers_rep
with replicate at SYDNEY_DS.pubs2
without purge
with suspension

If you have a warm standby application for the replicate database, you may want to suspend the connection for the active database only, and leave the standby DSI up. This way, Replication Server will replicate your row deletion transactions from the active replicate database to the standby database. In this case, use a command like this:

drop subscription publishers_sub
for publishers_rep
with replicate at SYDNEY_DS.pubs2
without purge
with suspension at active replicate only