create applied function replication definition
Creates an applied function replication definition and a user-defined function for a stored procedure that is to be replicated. The applied function is applied at the replicate database by the maintenance user.
create applied function replication definition <repdef_name>
with primary at <dataserver>.<database>
[with all functions named ‘<func_name>’ |
[[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]The applied function replication definition name. The name must conform to the rules for identifiers.
Specifies the primary data server and the primary database.
The name of the data server containing the primary data. If the primary database is part of a warm standby application, <dataserver> is the logical data server name.
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.
Specifies the stored procedure name at the primary and replicate databases.
The function name. <func_name> is a character string with a maximum length of 255 characters.
Specifies the stored procedure name at the primary database. with primary function named allows you to specify a name for the primary function that is different from the replication definition name. If you do not specify a primary function name, Replication Server uses the replication definition name as the name of the primary function.
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.
A parameter name from the function. A parameter name cannot appear more than once in the 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.
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.
Specifies a list of parameters that can be used in where clauses of define subscription, create subscription, or create article. You must include a pair of parentheses if you include the searchable parameters clause.
In a warm standby application, specifies whether to send to a standby database, all the parameters in the function (send standby all parameters) or only those specified in the replication definition (send standby replication definition parameters). The default is send standby all parameters.
- Creates an applied function replication definition named titles_frep for a function of the same name. The primary data is in the <pubs2> database of the <LDS> data server:
create applied 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)
- Creates an applied function replication definition named titles_frep for a function of the same name. The stored procedure is named upd_titles in the replicate database:
create applied function replication definition titles_frep with primary at LDS.pubs2 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)
- Creates an applied function replication definition named titles_frep for a function named upd_titles_prim. The stored procedure is named upd_titles_prim in the primary database and upd_titles in the replicate database:
create applied 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)
Use create applied 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 applied function replication definition is executed at the replicate site by the maintenance user while the function replicated through 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 an applied 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 both true, the existing request function replication definition is disabled. See the Replication Server Administration Guide Volume 2 for more information about applied function replication definition in Replication Server 15.0.1 and earlier.
Execute create applied function replication definition at the Replication Server that manages the database where the primary data is stored.
- Before executing create applied 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 applied function replication definition.
A connection exists between the Replication Server and the primary database. See create connection.
You can also create connections using rs_init. For more information, 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.
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”. For more information about the two types of replicated stored procedure, see sp_setrepproc.
Replication Server distributes the new function replication definition to qualifying sites through the replication system. The changes do not appear immediately at all qualifying sites because of normal replication system lag time.
User-Defined Functions and Function Strings
When you create an applied 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-defined base function-string class in which the user-defined function is used, and for each derived class that inherits from the base function-string class, use create function string to create a 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 stored procedure.
with replicate function named Clause
Use the with replicate function named clause to specify the name of the stored procedure to execute at the replicate database. 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 and 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.
Applied Function Replication Definitions for HDS Parameters
Although you cannot create function replication definitions that change the datatype of a parameter’s value, you can use HDS datatype definitions to declare parameters of applied 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 applied function replication definition to add parameters or searchable parameters to an existing applied 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 an applied 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
When replicating stored procedures using applied functions, create table replication definitions and subscriptions for the tables that are affected by the replicated stored procedures. This ensures that normal transactions and stored procedure executions that affect the tables are replicated. However, if a DML is inside a stored procedure that is marked as replicated, the DML is not replicated. In this case, subscribe to the stored procedure even if you have already subscribed to the table.
If you plan to use a function replication definition and a table replication definition for the same table, you can materialize the table data with the subscription for the table replication definition. You can then create the subscription for the function replication definition using create subscription with the without materialization clause.
Creating Multiple Replication Definitions
You can create multiple applied 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 applied function replication definitions created for the same primary function must use the same parameter with same name and the same datatype.
If an applied function replication definition specifies different names for the replication definition and the primary function, only Replication Server version 15.1 or later can subscribe to it.
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 replication definition is created.
In a warm standby database, the stored procedure has the same name as the active database, and the with replicate function clause is ignored. If one of the applied function replication definitions 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 a function replication definition for a primary function created with the send standby clause does not exist, 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 takes the name specified in the with replicate function named clause of the function replication definition, and includes parameters specified in the same function replication definition.