define subscription

Adds a subscription to the Replication Server system tables, but does not materialize or activate the subscription. The subscription may be for a database replication definition, a table replication definition, a function replication definition, or for a publication. This command begins the process of bulk subscription materialization, or the process of refreshing a publication subscription.

Syntax

define subscription sub_name
for {table_rep_def | function_rep_def |
     publication pub_name | database replication definition db_repdef  
     with primary at data_server.database} |
with replicate at data_server.database
     [where {column_name | @param_name}
     {< | > | >= | <= | = | &} value
     [and {column_name | @param_name}
     {< | > | >= | <= | = | &} value]...]
[subscribe to truncate table]
[for new articles]
[use dump marker]

Parameters

sub_name – The name of the subscription, which must conform to the rules for naming identifiers. The subscription name must be unique for the replication definition, where applicable, and for the replicate data server and database.
for table_rep_def – Specifies 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 publication pub_name – Specifies the publication the subscription is for.
for database replication definition db_repdef – Specifies 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 for a publication.
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.
where – Sets criteria for the column or parameter values that are to be replicated via the subscription. If you omit the where clause, all rows or parameters are replicated.
You can include a where clause in a subscription for a table or function replication definition. You cannot include a where clause in a publication subscription.

A where clause is composed of one or more simple comparisons, in which a searchable column or searchable parameter from the replication definition is compared to a literal value using one of these relational operators: <, >, <=, >=, =, or &. (The & operator is supported only for rs_address columns or parameters.) You can join comparisons with the keyword and.

Column or parameter names used in the expression must be included in the searchable columns list of the table replication definition or in the searchable parameters list of the function replication definition.

Java columns cannot be evaluated in subscription expressions. Thus, you cannot include a Java column of type rawobject or rawobject in row in a where clause.
column_name – A column name from the primary table, for a subscription to a table replication definition.
@param_name – A parameter name from a replicated stored procedure, for a subscription to a function replication definition.
value – A value for a specified column or parameter.
subscribe to truncate table – For a subscription to a table replication definition or to a publication, enables replication of the truncate table command to the subscribing replicate database.
You must set this option the same as it is set for any existing subscriptions that replicate data into the same replicate table. Otherwise, the new subscription will be rejected.
for new articles – Refreshes an existing subscription. Instructs Replication Server to check the subscription against the publication and then to create subscriptions against unsubscribed articles.
use dump marker – Tells Replication Server to apply transactions to a replicate database. use dump marker activates and validates the database subscription automatically. Without this option, users must activate and validate the database subscription manually.
Note: Use dump marker one at a time as you cannot define multiple database subscriptions with dump marker. You also need to place a dump database command between each subscription command. If you are using the cross platform dump and load (XPDL) feature in an MSA replication, avoid using the use dump marker clause for materialization.

Examples

Example 1 – Creates a subscription named titles_sub. It specifies that rows from the titles table with columns of the type “business” are to be replicated in the titles table in the pubs2 database of the data server named SYDNEY_DS:
```
define subscription titles_sub
  for titles_rep
    with replicate at SYDNEY_DS.pubs2
    where type = 'business'
```
Example 2 – Creates a subscription named titles_sub that includes rows from the titles table with prices that are greater than or equal to $10.00:
```
define subscription titles_sub
  for titles_rep
    with replicate at SYDNEY_DS.pubs2
    where price >= $10.00
```
Example 3 – Creates a subscription named myproc_sub for the function replication definition myproc_rep:
```
define subscription myproc_sub
  for myproc_rep
    with replicate at SYDNEY_DS.pubs2
```

Example 4 – Creates a subscription named pubs2_sub for the publication pubs2_pub:

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

Example 5 – Creates a subscription pubs2_sub for the database replication definition pubs2_rep:

define subscription pubs2_sub
  for database replication definition pubs2_rep 
    with primary at NEWYORK_DS.pubs2
    with replicate at TOKYO_DS.pubs2
    subscribe to truncate table
    use dump marker

Refer to the Replication Server Design Guide for examples of creating subscriptions for a complete replication system.

Usage

Use define subscription to create a subscription manually using bulk materialization. With bulk materialization, subscription creation and materialization is performed in discrete steps so that you can load the initial data from media rather than sending it from the primary database through the WAN.
If you have added any new articles to a publication with an existing subscription, you must refresh the publication subscription in order to create new subscriptions for these articles.
Activate the subscription using activate subscription and validate the subscription using validate subscription.
Although you can create multiple replication definitions for the same primary table, you cannot subscribe to more than one replication definition for the same replicate table. However, you can subscribe to the same replication definition more than once.

Subscribing to Publications

You can create a subscription for a valid publication to begin replication to a replicate database. All forms of subscription materialization are supported.
Use define subscription to create new article subscriptions in the publication subscription. Then use activate subscription, manually load the subscription data for the new article subscriptions, and use validate subscription to validate the publication subscription.
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.
When you activate and validate a publication subscription, all of its article subscriptions are activated and validated at the same time.
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.

Subscribing to Database Replication Definitions

When you create a database subscription, you cannot use the where clause to limit data subscription. All data is subscribed.
With database subscriptions, you can use only the no materialization or bulk materialization methods. Use define subscription to use dump and load or other bulk materialization method. Use create subscription to use the no materialization method.
You cannot subscribe to more than one database replication definition from the same origin.

Replicating Truncate Table

When you create the first subscription for a table, you can either include or not include the subscribe to truncate table option. Each subsequent subscription that copies information into the same table must follow the example of the first subscription. Otherwise, it will be rejected when you try to create it.
You can view or change the current “subscribe to truncate table” status of a particular replicate table by executing sysadmin apply_truncate_status.

Working with the rs_address Datatype

See create subscription for information about working with columns or parameters that use the rs_address datatype.

Requirements for Executing define subscription

In addition to the permissions listed below, make sure these requirements are met before you execute this command.

For a subscription to a table replication definition:
- A replication definition exists for the primary table you are replicating, and the table is marked for replication with sp_setreptable.
- Tables referenced in the replication definition exist in both the primary and the replicate database. Each table has the columns and datatypes defined in the replication definition.
  
  This table is also visible to the user creating the subscription and the user maintaining it. The easiest way to achieve this is to have the Database Owner create the table.
For a subscription to a function replication definition:
- A replication definition exists for the stored procedure you are replicating, and the stored procedure is marked for replication with sp_setrepproc.
- Stored procedures referenced in the function replication definition exist in both the primary and replicate database. Each table has the parameters and datatypes defined in the function replication definition.
For a subscription to a publication:
- A publication exists that contains articles for the primary tables or stored procedure you are replicating. The articles specify replication definitions that meet the requirements described above.
- The publication is valid.

Creating Subscriptions Using Define Subscription

You can use define subscription to subscribe to a table replication definition, a function replication definition, or a publication.
- For a subscription to a table replication definition, enter define subscription at the Replication Server that manages the database where the replicate data is to be stored.
- For a subscription to a function replication definition, enter define subscription at the Replication Server that manages the database where the destination stored procedure is to be executed via applied function delivery.
- For a subscription to a publication, enter define subscription at the Replication Server that manages the database where the replicate data is to be stored or where destination stored procedures are to be executed.
A table subscription maintains a replicate copy of a table, or selected rows from a table, in a database. Changes made to the primary version are also applied to the copy.
A function subscription replicates user-defined function invocations associated with a function replication definition. A replicated function typically includes parameters and modifies data, but it needs not involve replicated data.
A publication subscription involves underlying subscriptions for the articles the publication contains, which replicate table or user-defined function invocations depending on the replication definitions in the article.
See the Replication Server Administration Guide Volume 1 for more information about subscriptions and the role they play in replication.

Alternative Command to create subscriptions

Use create subscription to create, materialize, activate, and validate, in a single step, a subscription for a table replication definition, function definition replication, or publication.

Permissions

To execute define subscription, you must have the following login names and permissions:

The same login name and password at the replicate Replication Server, primary Replication Server, and primary database.
“create object” or “sa” permission at the replicate Replication Server where you enter this command.
“create object”, “primary subscribe”, or “sa” permission at the primary Replication Server.