CREATE SUBSCRIPTION statement [SQL Remote]

Creates a subscription for a user to a publication.

Syntax

CREATE SUBSCRIPTION
TO publication-name [ ( subscription-value ) ]
FOR subscriber-id

publication-name : identifier

subscription-value : string

subscriber-id : string

Parameters

publication-name
The name of the publication to which the user is being subscribed. This can include the owner of the publication.
subscription-value
A string that is compared to the subscription expression of the publication. The subscriber receives all rows for which the subscription expression matches the subscription value.

You can specify a variable name for subscription-value. If the variable is NULL, the value behaves as if an empty string was specified.
subscriber-id
The user ID of the subscriber to the publication. At the consolidated database, when you create a subscription to a remote user, the remote user must have been granted REMOTE privilege. At the remote database, when you create a subscription to the consolidated user, that user must have been granted CONSOLIDATED privilege.

Remarks

In SQL Remote, publications and subscriptions are two-way relationships. If you create a subscription for a remote user to a publication on a consolidated database, you should also create a subscription for the consolidated user to a publication on the remote database. By default, the Extraction utility (dbxtract) and the Extract Database Wizard grant the appropriate PUBLISH and CONSOLIDATE privilege to users in the remote databases.

If subscription-value is supplied, it is matched against each SUBSCRIBE BY expression in the publication. The subscriber receives all rows for which the value of the expression is equal to the supplied string.

Note For required parameters that accept variable names, an error is returned if one of the following conditions is true:

The variable does not exist
The contents of the variable are NULL
The variable exceeds the length allowed by the parameter
The data type of the variable does not match that required by the parameter

Privileges

You must have the SYS_REPLICATION_ADMIN_ROLE system role.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following statement creates a subscription for the user p_chin to the publication pub_sales. The subscriber receives all rows for which the subscription expression has a value of Eastern.

CREATE SUBSCRIPTION
TO pub_sales ( 'Eastern' )
FOR p_chin;

The following statement creates a subscription for user name Sam_Singer to the CustomerPub publication, which was created using a WHERE clause:

CREATE SUBSCRIPTION 
  TO CustomerPub
  FOR Sam_Singer;

The following statement creates a subscription for user name Sam_Singer to the PubOrders publication, defined with a subscription expression SalesRepresentative, requesting the rows for Sam Singer's own sales:

CREATE SUBSCRIPTION 
  TO PubOrders ( '856' )
  FOR Sam_Singer;

Example

The following statement creates a variable for the value 'ny':

CREATE VARIABLE @state LONG VARCHAR ;
SET @state = 'ny' ;

The following statement creates a publication called pub_customer:

CREATE PUBLICATION pub_customer (
TABLE DBA>Customer ( ID, company_name, City, State )
SUBSCRIBE BY State ) ;

The following statement creates a subscription for user name Sam_Singer

CREATE SUBSCRIPTION 
   TO pub_customer ( @dan ) 
   FOR Sam_Singer ;

The subscriber receives all rows for which the subscription expression has a value of the variable @dan, which is set to NY state.