create subscription

Description

Creates and initializes a subscription and materializes subscription data. The subscription may be for a database replication definition, table replication definition, function replication definition, or publication.

Syntax

create subscription sub_name
for {table_repdef | func_repdef | {publication pub |
     database replication definition db_repdef}
     with primary at server_name.db}
with replicate at data_server.database
[where {column_name | @param_name}
     {< | > | >= | <= | = | &} value
[and {column_name | @param_name}
     {< | > | >= | <= | = | &} value]...]
[without holdlock | incrementally | without materialization]
[subscribe to truncate table]
[for new articles]

Parameters

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:

create 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:

create 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. In order to use this command to create a subscription for a function replication definition, data must already exist at the replicate database, and you must use the without materialization clause:

create subscription myproc_sub
 for myproc_rep
 with replicate at SYDNEY_DS.pubs2
 without materialization

Example 4

Creates a subscription named pubs2_sub for the publication pubs2_pub:

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

Example 5

Creates a database subscription pubs2_sub for the database replication definition pubs2_rep:

create subscription pubs2_sub
 for database replication definition pubs2_rep
   with primary at NEWYORK_DS.pubs2
   with replicate at TOKYO_DS.pubs2
 without materialization
 subscribe to truncate table

Usage

• To subscribe to a function or database replication definition, use create subscription with the without materialization clause, or use define subscription and the other bulk materialization commands.

• Execute create subscription at the Replication Server of the database where the replicated data will be stored.

• See the Replication Server Administration Guide Volume 1, for more information about subscriptions and the role they play in replication.

• If you need to change which replication definition a subscription is for, you must drop the subscription and re-create it, specifying the name of the replication definition to which you want to subscribe.

• You can create multiple replication definitions for the same primary table or database. You cannot subscribe to more than one replication definition for the same replicate table or database, although you can subscribe to the same replication definition more than once.

• If you want to materialize text, unitext, image, or rawobject data, you can use automatic materialization only if the size of your data row is less than 32K. Otherwise, you must use bulk materialization.

• 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.

• If your replicate Replication Server is at lower version than your primary Replication Server, you cannot create a database subscription at the replicate Replication Server for a primary database controlled by the primary Replication Server.

• To successfully create a table replication definition for a primary database that is subscribed by a database subscription, the replicate Replication Server must be at the same or higher version as the table replication definition.

• When a publication is valid, you can create a subscription for the publication in order to begin replication to a replicate database. All forms of subscription materialization are supported.

• 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 use atomic or non-atomic materialization, article subscriptions are materialized one at a time in the order that the articles were added to the publication.

  • When you use create subscription with the without materialization clause, all 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.

• When you create a subscription that includes a where clause, make sure that the value in the where clause comparison is in the declared datatype format.

• Subscriptions that specify columns subject to class- or column-level translations in the where clause cannot be dematerialized automatically. You must use either the bulk or the no-materialization method.

• When you create the first subscription, you can either include or not include the subscribe to truncate table option. Each subsequent subscription that replicates into the same table must follow the example of the first subscription. Otherwise, the subscription is rejected when you try to create it.

• You can change the current “subscribe to truncate table” status of a particular replicate table by executing sysadmin apply_truncate_table.

• In addition to the permissions listed below, make sure that these requirements are met before you execute create subscription.

  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.

  • If you subscribe to tables marked using sp_reptostandby, you must configure the primary database connection using the rep_as_standby configuration parameter and configure RepAgent using send_warm_standby_exacts.

  • 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 visible to the user creating the subscription and to 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 stored procedure 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.

• These requirements apply when you create subscriptions in warm standby applications:

  • If the destination database is part of a warm standby application, the table must exist in both the active and standby databases. Both tables must be marked for replication using sp_setreptable or sp_reptostandby.

  • For a logical primary database, you cannot create a subscription while Replication Server is in the process of adding a standby database.

• If a primary Adaptive Server database contains a replicated table and another table that has the same name, the owner of the second (unreplicated) table cannot create a subscription to the replicated table without using custom rs_select or rs_select_with_lock function strings. For example:

  • If there is a replication definition for a primary table named db.dbo.table1, and

  • Database user “jane” owns a table named db.jane.table1, then

  • Jane cannot create a subscription to the replication definition for db.dbo.table1 using the default function strings.

• The default method for materializing subscriptions with this command is atomic materialization. Atomic materialization locks the primary table and copies subscription data through the network in a single atomic operation.

• During atomic materialization, no rows appear at the replicate database until the select transaction has been completed in the primary database. If the subscription specifies a large number of rows, the select transaction can run for a long time, causing a delay at the replicate site.

• If you plan to use the atomic method of subscription materialization:

  • You or the Database Owner must own the primary table, or you must use user-defined function strings for select operations at the primary database.

  • The Database Owner or the maintenance user must own the replicate table, or you must use user-defined function strings for select operations at the replicate database. If the owner of the replicate table is different from the owner of the primary table, you must create a unique function string by using a distinct function-string class.

• The without holdlock or incrementally options are alternatives to the default atomic method of subscription materialization. When you specify these options, Replication Server applies the rows in batches, so that data appears at the replicate database a batch at a time.

  As a result, during materialization, queries at the replicate database may return incomplete data for the subscription. This temporary condition ends when check subscription indicates the subscription is valid.

• The incrementally option is a variation of atomic materialization. Use this option for large subscriptions to avoid a long-running transaction at the replicate database. The subscription data is not applied atomically at the replicate database, so the data is available; however, it is incomplete until materialization has completed and the subscription is validated.

• When incrementally is used, the select is performed with a holdlock to maintain serial consistency with the primary database. The replicate table passes through states that occurred previously at the primary database.

  In all cases, replicate data is consistent with the primary database by the time materialization completes and check subscription indicates that the subscription is valid.

• The without holdlock option uses non-atomic materialization. When this option is specified, materialization rows are selected from the primary database without a holdlock. This can introduce inconsistency if rows are updated at the primary database after the select. To correct inconsistencies, use set autocorrection on when using without holdlock.

• When data already exists at the replicate database, you can use atomic or non-atomic materialization instead of bulk materialization.

• If you plan to use non-atomic method of subscription materialization:

  • Do not use without holdlock if you update data by distributing applied functions from the primary database or if you update the data with commutative functions. For example, if a stored procedure updates a row by incrementing the previous value of a column, the value may be incorrect when materialization has completed.

  • If the replicate minimal columns option is set for a replication definition, you cannot use without holdlock to create new subscriptions.

  • For non-atomic subscriptions, if a non-atomic subscription is materializing when switch active executes, it is marked “SUSPECT.

The without materialization clause specifies the no-materialization method. It provides an convenient way to create a subscription when the subscription data already exists at the replicate database.

• The subscription data must already exist at the replicate database.

• The primary and replicate database must be in sync.

• Activity must be stopped at the primary database so that there are no further updates in the Replication Server stable queue.

• You can subscribe to replication definitions whose columns or parameters use the special datatype rs_address. This datatype allows a unique subscription resolution method, whereby bitmaps of the rs_address datatype (based on the underlying int datatype) are compared with a bitmask in a subscription’s where clause. The bitmap comparison tells the primary Replication Server whether or not a replicate site should receive the data in each row.

• For rs_address columns or parameters only, the bitmap comparison operator & is supported in the where clause, as follows:

  where rs_address_column1  & bitmask
  [and rs_address_column2  & bitmask]
   [and other_search_conditions]
  

• Replication Server does not replicate a row if the only changed columns are rs_address columns, unless the changed bits indicate that the row should be inserted or deleted at the replicate database.

  Because of this filtering, rs_address columns in replicate databases may not be identical to the corresponding columns at the primary database. This optimizes applications that use rs_address columns to specify the destination replicate databases.

• Each bit in an rs_address column field may represent a category of data, such as inventory or billing. In a subscription bitmask, you set the corresponding bit to “on” (1), for each category of data you want to replicate to the subscribing site.

  For example, users at a warehouse site who are interested in inventory data would set the inventory bit to “on” in a subscription bitmap. If the same warehouse users are not interested in billing data, they would set that bit to “off” (0). When a bit is set to “on” in both a subscription bitmask and an rs_address column, the row containing the bit is replicated.

• Due to the 32-bit limitation of the underlying int datatype, you may need to construct primary tables with more than one rs_address column. The and keyword allows you to create a single subscription to perform bitmap comparisons on more than one rs_address column.

  However, to subscribe to a row when one or more bits are set in either of two or more rs_address columns, you must create separate subscriptions.

• When Replication Server materializes a subscription, it logs in to the primary data server, using the subscription creator’s login name, and selects the rows from the primary table. Use check subscription to monitor the progress of the materialization.

• create subscription returns a prompt before the data materialization is complete. Materialization is complete when check subscription reports “VALID” at the replicate Replication Server.

Permissions

To execute create 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 Adaptive Server 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.

• select permission on the primary table in the primary Adaptive Server database.

• execute permission on the rs_marker stored procedure in the primary Adaptive Server database.

• The replicate database maintenance user must have select, insert, update, and delete permissions on the replicate table, and execute permissions for functions used in replication.

See also