alter function replication definition

Changes an existing function replication definition created by the create function replication definition command.

Note: Support for create function replication definition and alter function replication definition are scheduled to be discontinued. Sybase suggests that you use these commands instead:

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

Syntax

alter function replication definition function_rep_def
{
   deliver as 'proc_name' |
   add @param_name datatype [, @param_name datatype]... |
   add searchable parameters @param_name[, @param_name]... |
   send standby {all | replication definition}
   parameters
}

Parameters

function_rep_def – The name of the function replication definition to be altered.
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 optional clause, the function is delivered as a stored procedure with the same name as the function replication definition.
add – Specifies additional parameters and their datatypes for the function replication definition.
@param_name – The name of a parameter to be added to the list of replicated parameters or searchable parameters. Each parameter name must begin with a @ character.
datatype – The datatype of the parameter you are adding to a parameter list. See Datatypes. for a list of supported datatypes and their syntax. Adaptive Server stored procedures and function replication definitions may not contain parameters with the text, unitext, and image datatypes.
add searchable parameters – Specifies additional parameters that can be used in the where clauses of the define subscription or define subscription command.
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 – Adds three parameters to the titles_frep function replication definition: a varchar parameter named @notes, a datetime parameter named @pubdate, and a bit parameter named @contract:
```
alter function replication definition titles_frep
 add @notes varchar(200), @pubdate datetime,
 @contract bit
```
Example 2 – Adds the @type and @pubdate parameters to the list of searchable parameters in the titles_frep function replication definition:
```
alter function replication definition titles_frep
 add searchable parameters @type, @pubdate
```
Example 3 – Changes the titles_frep function replication definition to be delivered as the newtitles stored procedure at the destination database, typically the primary database (used for request function delivery):
```
alter function replication definition titles_frep
 deliver as 'newtitles'
```

Usage

alter function replication definition changes a function replication definition by adding replicated parameters, adding searchable parameters, specifying whether to send all parameters to the warm standby, or specifying a different name for the stored procedure to execute in the destination database.
The name, parameters, and datatypes you specify for a function replication definition you are altering must match the stored procedure you are replicating. You can specify only those parameters you are interested in replicating.
You must execute alter function replication definition at the Replication Server that manages the primary database (where you created the function replication definition).
A parameter name must not appear more than once in any clause.
If you are adding parameters, coordinate alter function replication definition with distributions for the function replication definition. Follow the steps described in "Altering a Function Replication Definition" to avoid errors.
You can 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. Typically, you use this option in request function delivery. For more information, see create connection.

See the Replication Server Administration Guide Volume 1 for more information on alter function replication definition.

Altering a Function Replication Definition:

Quiesce the replication system using Sybase Central Replication Manager plug-in or the procedure described in the Replication Server Troubleshooting Guide.
Ideally, you should first quiesce primary updates and ensure that all primary updates have been processed by the replication system. If you are unable to do that, then old updates in the primary log will not have values for new parameters, and the replication system will use nulls instead. You may need to take this into account when altering function strings in step 4 below.
Alter the stored procedure at the primary and the replicate sites.
Alter the function replication definition. Wait for the modified function replication definition to arrive at the replicate sites.
If necessary, alter any function strings pertaining to the function replication definition. Wait for the modified function strings to arrive at the replicate sites.
If necessary, modify subscriptions on the function replication definition at replicate sites. To modify a subscription, drop it and re-create it using drop subscription and create subscription (with no materialization option).
Altering a replication definition does not affect current subscriptions. If new parameters are added to the function replication definition, they are replicated with any new updates for all existing subscriptions.
Resume updates to the data at the primary database.

Permissions

alter function replication definition requires “create object” permission.