create applied function replication definition

Description

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.

Syntax

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]

Parameters

Examples

Example 1

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)

Example 2

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)

Example 3

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)

Usage

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

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.

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.

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.

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

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.

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

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

Permissions

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

See also