create function replication definition

Creates a function replication definition and user-defined function for a stored procedure that is to be replicated.

Note

create function replication definition and alter function replication definition are deprecated commands. SAP suggests that you use these instead:

create applied function replication definition and alter applied function replication definition.
create request function replication definition and alter request function replication definition.

Syntax

create function replication definition
        <function_rep_def
>with primary at <data_server>.<database
>[deliver as '<proc_name>']
        ([@<param_name> <datatype >[, @<param_name> <datatype>]...])
        [searchable parameters (@<param_name
        > [, @<param_name>]...)]
        [send standby {all | replication definition}
        parameters]

Parameters

function_rep_def: A name for the function replication definition. It must conform to the rules for identifiers.
with primary at: Specifies the data server and database containing the primary data.
data_server: The name of the data server containing the primary data. If the primary database is part of a warm standby application, <data_server> is the logical data server name.
database: The name of the database containing the primary data. If the primary database is part of a warm standby application, <database> is the logical database name.
deliver as: Specifies the name of the stored procedure to execute at the database where you are delivering the replicated function. <proc_name> is a character string of up to 200 characters. If you do not use this clause, the function is delivered as a stored procedure with the same name as the function replication definition.
@param_name: A parameter name from the function. A parameter name must not appear more than once in each clause in which it appears. You are not required to include parameters and their datatypes, but you must include the parentheses ( ) for this clause, whether or not you include any parameters.
datatype: The datatype of a parameter in the function. See Datatypes for a list of the datatypes and their syntax. Adaptive Server stored procedures and function replication definitions cannot contain parameters with the <text>, <unitext>, <rawobject>, and <image> datatypes.
searchable parameters: Specifies a list of parameters that can be used in where clauses of define subscription, create subscription, or create article. You must include the parentheses ( ) if you include this clause.
send standby: In a warm standby application, specifies whether to send all parameters in the function (send standby all parameters) or just those specified in the replication definition (send standby replication definition parameters) to a standby database. The default is send standby all parameters.

Examples

Example 1

Creates a function replication definition named <titles_frep> for a function and stored procedure of the same name. The primary data is in the <pubs2> database in the LDS data server. Use a function replication definition like this for an applied function:

create function replication definition titles_frep
 with primary at LDS.pubs2
 (@title_id varchar(6), @title varchar(80),
  @type char(12), @pub_id char(4),
  @price money, @advance money,
  @total_sales int)
  searchable parameters (@title_id, @title)

Example 2

Creates a function replication definition named <titles_frep> for a function and stored procedure of the same name, as in the previous example. In this case, the stored procedure to be invoked in the destination database is named <upd_titles>. Use a function replication definition like this for a request function:

create function replication definition titles_frep
 with primary at LDS.pubs2
 deliver as 'upd_titles'
 (@title_id varchar(6), @title varchar(80),
  @type char(12), @pub_id char(4),
  @price money, @advance money,
  @total_sales int)
  searchable parameters (@title_id, @title)

Usage

Use create function replication definition to describe a stored procedure that is to be replicated. For an overview of replicated stored procedures, see the Replication Server Administration Guide Volume 1.
Execute create function replication definition at the Replication Server that manages the database where the primary data is stored.
You can create only one function replication definition per replicated stored procedure.
Before executing this command, be sure that:
- The function replication definition name is unique in the replication system. Replication Server cannot always enforce this requirement when you use create function replication definition.
- A connection exists from the Replication Server to the database where the primary data is stored. See create connection for more information. You can also create connections using rs_init. Refer to the Replication Server installation and configuration guides for your platform.
- The name, parameters, and datatypes you specify for the function replication definition match those of the stored procedure involved. You can specify only those parameters you are interested in replicating.
Unlike replicated stored procedures associated with table replication definitions, stored procedures associated with function replication definitions are not required to update a table. This allows you to replicate transactions that are not associated with replicated data. For more information about stored procedures, see RSSD Stored Procedures.

See sp_setrepproc for more information on the two types of replicated stored procedures.
Replication Server distributes the new function replication definition to qualifying sites through the replication system. The changes do not appear immediately at all such sites because of normal replication system lag time.

User-Defined Functions and Function Strings

When you create a function replication definition, Replication Server automatically creates a corresponding user-defined function.
For the system-provided function-string classes in which the user-defined function associated with this function replication definition will be used, and for each derived class that inherits from these classes, Replication Server generates a default function string for the user-defined function.
You can customize the function string in <rs_sqlserver_function_class> and in user-created function-string classes using create function string.
For each user-created base function-string class in which the user-defined function will be used, and for each derived class that inherits from such a class, you must create a function string, using create function string. The function string should invoke a stored procedure or RPC, with language appropriate for the replicate data server.
For an overview of function-string classes, function strings, and functions, see the Replication Server Administration Guide Volume 2.

The with primary at Clause

Use the with primary at clause to specify the data server and database containing the primary data. This is not necessarily the database that contains the invoked stored procedure.

For applied functions (primary-to-replicate function replication) and request functions (replicate-to-primary function replication), create the function replication definition at the Replication Server managing the primary data, and specify the primary database using the with primary at clause.

The deliver as Clause

Use the optional deliver as clause to specify the name of the stored procedure to execute at the destination database where you are delivering the replicated function. If you do not use this clause when you create or alter the function replication definition, the function is delivered as a stored procedure with the same name as the function replication definition.

In a warm standby database the stored procedure has the same name as in the active database so the deliver as clause is ignored.

Typically, you would use the deliver as clause for request function delivery; that is, when a function is replicated from a replicate Replication Server to a primary Replication Server. This way, the name of the replicated function is not the same as the stored procedure that is executed.

Use this method with “round-trip” stored procedure replication, where the primary Replication Server that is the destination for the request function executes an applied function, to which the originating replicate Replication Server in turn subscribes.

See the Replication Server Administration Guide Volume 1 for more information.

Function Replication Definitions for HDS Parameters

Although you cannot create function replication definitions that alter the datatype of a parameters value, you can use HDS datatype definitions to declare parameters for applied function replication definitions. Such parameters are then subject to class-level translations. See the Replication Server Administration Guide Volume 1 for more information about HDS.
Replication Server does not perform translations on parameter values for request functions. Note, however, that during function-string mapping Replication Server uses the delimiters defined for the parameter values of their declared datatype to generate SQL.

Altering Function Replication Definitions

Use alter function replication definition to add parameters or searchable parameters to an existing function replication definition. You can also specify a new stored procedure name to use when delivering the replicated function at the destination database.
If you need to remove or rename parameters in function replication definition, you must drop all subscriptions to the function replication definition (applied functions only). Then drop the function replication definition and re-create it.

Subscribing to Function Replication Definitions

In order to subscribe to a function replication definition, use create subscription with the without materialization clause, or use define subscription and the other commands involving bulk materialization.

Function Replication Definitions and Table Replication Definitions

In replicating stored procedures through applied functions, it is advisable to create table replication definitions and subscriptions for the same tables that the replicated stored procedures will affect. By doing this, you can ensure that any normal transactions that affect the tables will be replicated as well as the stored procedure executions.

DML inside stored procedures marked as replicated is not replicated through table replication and you must subscribe to the stored procedure even if you have subscribed to the table.
If you plan to use both kinds of replication definition for the same table, you can materialize the table data with the subscription for the table replication definition. Then you can create the subscription for the function replication definition using create subscription with the without materialization clause.

Permissions

create function replication definition requires “create object” permission.