create request function replication definition

Creates a request function replication definition and a user-defined function for a stored procedure that is to be replicated. The request function is applied at the replicate database by the same user who executes the stored procedure at the primary database.

Syntax

create request function replication definition repdef_name
        with primary at dataserver.database
        with primary function named ‘func_name’
        with replicate function named ‘func_name’
        ([@param_name datatype [, @param_name datatype]…])
        [searchable parameters (@param_name [, @param_name]…)]
         [send standby {all | replication definition} parameters]

Parameters

repdef_name – The function replication definition name. The name must conform to the rules for identifiers.
with primary at – Specifies the data server and database that contains the primary data.
dataserver – The name of the data server that contains the primary data. If the primary database is part of a warm standby application, dataserver is the logical data server name.
database – The name of the database that contains the primary data. If the primary database is part of a warm standby application, database is the logical database name.
with primary function named – Specifies the stored procedure name at the primary database. If you do not specify a primary function name, Replication Server uses the replication definition name as the name of the primary function. The primary function name must be different from the replicate function name specified in the with replicate function named clause.
'func_name' – The name of the function, with a maximum length of 255 characters.
with replicate function named – Specifies the name of the stored procedure to execute at the replicate database. If you do not specify a replicate function name, Replication Server uses the replication definition name as the name of the replicate function. The replicate function name must be different from the primary function name specified in the with primary function named clause.
Note: The primary stored procedure refers to the stored procedure originally invoked by the client, while the replicate stored procedure refers to the stored procedure replicated from the primary database and invoked by the replicate Replication Server.
This request function behavior is different from the request function behavior in Replication Server 15.0.1 and earlier. For more information about the behavior of request function in Replication Server 15.0.1 and earlier versions, see the Replication Server Administration Guide Volume 2.
@param_name – A parameter name from the function. A parameter name cannot 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 a pair of parentheses, 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 to the standby database, all the parameters in the function (send standby all parameters) or just those specified in the replication definition (send standby replication definition parameters). The default is send standby all parameters.

Examples

Example 1 – Creates a request function replication definition named titles_frep for a function named upd_titles_prim. The stored procedure to be invoked in the destination database is named upd_titles:

create request function replication definition titles_frep
with primary at LDS.pubs2
with primary function named 'upd_titles_prim'
with replicate function named '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 request function replication definition to describe a stored procedure that you want to replicate. The difference between the applied function replication definition and the request function replication definition is that the function replicated through an applied function replication definition is executed at the replicate site by the maintenance user while the function replicated through a request function replication definition is executed at the replicate site by the same user who executes the primary function at the primary site. For an overview of replicated stored procedures, see the Replication Server Administration Guide Volume 1.
When you create a request function replication definition for a primary function, make sure that the function does not already have an existing function replication definition that satisfies both these conditions:
- Was created using the create function replication definition command
- The function replication definition is used for the request function replication without subscription in Replication Server 15.0.1 and earlier version
If these conditions are true, the existing request function replication definition is disabled. See the Replication Server Administration Guide Volume 2 for more information about request function replication definition in Replication Server 15.0.1 and earlier.
Execute the create request function replication definition command at the Replication Server that manages the database where the primary stored procedure is stored.
Before executing create request function replication definition, 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 request function replication definition.
- A connection exists from the Replication Server to the database where the primary data is stored. See create connection. You can also create connections using rs_init; see the Replication Server Installation Guide and the Replication Server Configuration Guide for your platform.
- The name, parameters, and datatypes you specify for the function replication definition must match those of the stored procedure involved. Only the parameters specified in the function replication definition are replicated.
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 request function replication definition, Replication Server automatically creates a corresponding user-defined function. Similarly, in rs_sqlserver_function_class, Replication Server automatically creates a default function string for the user-defined function.
You can customize the function string in rs_sqlserver_function_class and in user-defined 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.

with primary at Clause

Use the with primary at clause to specify the primary data server and database. The primary database is the database that contains the invoked primary stored procedure.

with replicate function named Clause

Use the with replicate function named clause to specify the name of the stored procedure you want to execute at the destination database where you are delivering the replicated function. If you do not use with replicate function named 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 with replicate function named is ignored.

A round-trip replication enables a database to send a data change request to another database and to replicate the data change back to the requesting database. See the Replication Server Administration Guide Volume 1 for more information about how to set up a round-trip replication with both applied and request function replication definitions.

Request Function Replication Definitions for HDS Parameters

Although you cannot create function replication definitions that alter the datatype of a parameter’s value, you can use HDS datatype definitions to declare parameters for request function replication definitions. The declared parameters are subjected to class-level translations.

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

Altering Function Replication Definitions

Use alter request function replication definition to add parameters or searchable parameters to an existing request function replication definition. You can also specify a different replicate name for the function.
To remove or rename parameters in function replication definition, drop all subscriptions to the function replication definition. After dropping the subscriptions, drop the function replication definition and re-create it.

Subscribing to Function Replication Definitions

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

Creating Multiple Replication Definitions

You can create multiple request function replication definitions for the same primary function, and customize each one so that it can be subscribed to by a different replicate function. See the Replication Server Administration Guide Volume 1 for details.
Different request function replication definitions created for the same primary function must use the same parameter with same name and the same datatype.
A request function replication definition can only be subscribed to Replication Servers version 15.1.
The same primary function can have applied function replication definitions or request function replication definitions, but not both. The function replication definition created with the create function replication definition command is considered as an applied function at the primary Replication Server where the function is created.
In a warm standby database, the stored procedure has the same name as the active database, and the with replicate function named clause is ignored. If one of the request function replication definition is created with the send standby replication definition parameters clause, the parameters specified in the function replication definition are delivered to the standby database. Otherwise, all of the parameters in the primary function are delivered.
In an MSA environment, if there is no function replication definition for a primary function created with the send standby clause, the function delivered to the replicate database has the same name as the primary function with all the primary function’s parameters. Otherwise, the function delivered to the replicate database has the name specified in the with replicate function named clause of that function replication definition, and with parameters specified in the same function replication definition.

Permissions

create request function replication definition requires “create object” permission.