create function string

Adds a function string to a function-string class. Replication Server uses function strings to generate instructions for data servers.

Syntax

create function string
        [replication_definition.]function[;function_string]
        for function_class [with overwrite]
        [scan 'input_template']
        [output
        {language 'lang_output_template' |
        rpc 'execute procedure
        [@param_name=]{constant |?variable!mod?}
                [, [@param_name=]
                        {constant |?variable!mod?}]...' |
        writetext [use primary log | with log | no log] | 
        none}]

Parameters

Examples

Usage

  • Use create function string to add a function string to a function-string class. Function strings contain the database-specific instructions needed by Replication Server to convert a function to a command for a database.

  • For an overview of functions, function strings, and function-string classes, see the Replication Server Administration Guide Volume 2.

  • Create or alter function strings for functions with class scope at the primary site for the function-string class. See create function string class for more information about the primary site for a function-string class.

  • Create or alter function strings for functions with replication definition scope, including user-defined functions, at the site where the replication definition was created. Each replication definition has its own set of function strings.

  • Replication Server distributes the new function string to qualifying sites through the replication system. The changes do not appear immediately at all such sites because of normal replication system lag time.

  • Some function strings are generated dynamically; they are not stored in the RSSD.

Function Strings and Function-String Classes

  • For each of the system-provided function-string classes in which a function will be used, and for each derived class that inherits from these classes, Replication Server generates a default function string for the function. This is true for both system functions and user-defined functions. (Default function strings for the rs_dumpdb and rs_dumptran functions are not provided. You only need to create them if you are using coordinated dumps.

  • Customize the function string in rs_sqlserver_function_class using alter function string. Customize the function string in user-created function-string classes using create function string.

  • For each user-created base function-string class in which the function will be used, and for each derived class in which you want to override the inherited function string, you must create a function string, using create function string.

  • Omitting the output clause instructs Replication Server to generate a function string in the same way that it generates function strings for the rs_sqlserver_function_class or rs_default_function_class function-string classes.

  • The default function string for a user-defined function is an invocation of a stored procedure where the name is the function name and the parameters are the function parameters. The stored procedure is executed as a language command, not as an RPC.

Function Strings and Replicate Minimal Columns

  • If you have specified replicate minimal columns for a replication definition, you cannot normally create non-default function strings for the rs_update, rs_delete, rs_get_textptr, rs_textptr_init, or rs_datarow_for_writetext system functions.

    However, you can create non-default function strings for the rs_update and rs_delete functions if you use the rs_default_fs system variable within the function string. This variable represents the default function-string behavior. You can add additional commands to extend the function-string behavior.

  • See create replication definiton for more information about the replicate minimal columns option.

Input and Output Templates
  • Depending on the function, function strings can have input and output templates. Replication Servers substitute variable values into the templates and pass the result to data servers for processing.
  • Input and output templates have the following requirements:
    • They are limited to 64K. The result of substituting run-time values for embedded variables in function-string input or templates must not exceed 64K.
    • Input templates and language or RPC output templates are delimited with two single quote characters (').
    • Variable names in input templates and output templates are delimited with question marks (?).
    • A variable name and its modifier are separated with an exclamation mark (!).
  • When creating function strings:
    • Use two consecutive single quote characters ('') to represent one literal single quote character within or enclosing data of character or date/ time datatypes, as shown for “Berkeley” in the following character string:
      'insert authors
      (city, au_id, au_lname, au_fname)
      values (''Berkeley'', ?au_id!new?,
      ?au_lname!new?,
      ?au_fname!new?)'
    • Use two consecutive question marks (??) to represent one single question mark within data of character datatypes.
    • Use two consecutive semi-colons (;;) to represent one single semicolon within data of character datatypes.
Input Templates
  • Input templates are used only with the rs_select and rs_select_with_lock functions, which are used during non-bulk subscription materialization and with purge subscription dematerialization. Replication Server matches the subscription’s where clause with an input template to find the function string to use.
  • Input templates have the following requirements:
    • They contain only user-defined variables, whose values come from the constants in the where clause. The user-defined variables can also be referenced in the function string’s output template.
    • If the input_template is omitted, it can match any select command. This allows you to create a default function string that is executed when no other function string in the function-string class has an input_template matching the select command.
Output Templates
  • Output templates determine the format of the command sent to a replicate data server. Most output templates can use one of these formats: language RPC, or none. An output template for an rs_writetext function string can use the RPC format or the additional formats writetext or none. For a description of these formats, see the Replication Server Administration Guide Volume 2.
  • When Replication Server maps function string output templates to data server commands, it formats the variables using the format expected by Adaptive Server. It modifies datatypes for modifiers that do not end in _raw (the modifiers that are normally used), as follows:
    • Adds an extra single quote character to single quote characters appearing in character and date/time values to escape the special meaning of the single quote character.
    • Adds single quote characters around character and date/time values, if they are missing.
    • Adds the appropriate monetary symbol (the dollar sign in U.S. English) to values of money datatypes.
    • Adds the “0x” prefix to values of binary datatypes.
    • Adds a combination of a backslash (\) and newline character between existing instances of a backslash and newline character in character values. Adaptive Server treats a backslash followed by a newline as a continuation character, and therefore deletes the added pair of characters, leaving the original characters intact.

      Replication Server does not modify datatypes in these ways for modifiers that end in _raw.

      Formatting for Function String Variables table summarizes how Replication Server formats each datatype for the modifiers that do not end in _raw:

Formatting for Function String Variables
Datatype Formatting of Literals
bigint, int, smallint, tinyint, rs_address Integer number
unsigned bigint, unsigned int, unsigned smallint, unsigned tinyint Unsigned Integer number
decimal, numeric, identity Exact decimal number
float, real Decimal number
char, varchar

Enclosed in single quote character

Adds single quote character to any instance of a single quote character

Pads instances of backslash + newline characters

unichar, univarchar Unicode
money, smallmoney Adds the appropriate money symbol (dollar sign for U.S. English)
date, time, datetime, smalldatetime

Enclosed in single quote characters

Adds single quote character to any instance of a single quote character

binary, timestamp, varbinary Prefixed with 0x
bit 1 or 0
  • Output templates have the following requirements:
    • The result of substituting run-time values for embedded variables in function-string output templates must not exceed 64K.
    • You can put several commands in a language function-string output template, separating them with semicolons (;). If the database is configured to allow command batches, which is the default, Replication Server replaces the semicolons with that connection’s DSI command separator character before sending the function string in a single batch to the data server. The separator character is defined in the dsi_cmd_separator option of the alter connection command.

      To represent a semicolon that should not be interpreted as a command separator, use two consecutive semicolons (;;).

      If the connection to the database is not configured to allow batches, Replication Server sends the commands in the function string to the data server one at a time. To enable or disable batching for a database, use alter connection.

Replication Server System-Defined Variables table list the system-defined variables that can be used in function-string output templates. Use the sys or sys_raw modifier for these variables.

Replication Server System-Defined Variables
System Variable Datatype Description
rs_default_fs text The default generated function-string text for the function
rs_deliver_as_name varchar(200) For execution of a replicated function, name of the procedure to be invoked at the destination
rs_destination_db varchar(30) Name of the database where a transaction was sent
rs_destination_ds varchar(30) Name of the data server where a transaction was sent
rs_destination_ldb varchar(30) Name of the logical database where a transaction was sent
rs_destination_lds varchar(30) Name of the logical data server where a transaction was sent
rs_destination_ptype char(1) Physical connection type (“A” for active or “S” for standby) for the database where a transaction was sent
rs_destination_user varchar(30) User who will execute the transaction at the destination
rs_dump_dbname varchar(30) Name of the database where a database or transaction dump originated
rs_dump_label varchar(30) Label information for a database or transaction dump. For Adaptive Server, this variable holds a datetime value that is the time the dump originated.
rs_dump_status int(4)
Dump status indicator:
  • 0 – indicates that the dump transaction command does not contain the parameter with standby_access
  • 1 – indicates that the dump transaction command contains the parameter with standby_access
rs_dump_timestamp varbinary(16) Timestamp of a database or transaction dump
rs_lorigin int(4) ID of the originating logical database for a transaction
rs_isolation_level varchar(30) Transaction isolation level of a database connection.
rs_origin int(4) ID of the originating database for a transaction
rs_origin_begin_time datetime
The time that a command was applied at the origin
Note: If you execute select getdate() while ASE is still processing user database recovery, the returned value of select getdate() may be different from the value of rs_origin_begin_time.
rs_origin_commit_time datetime
The time that a transaction was committed at the origin
Note: If you execute select getdate() while ASE is still processing user database recovery, the returned value of select getdate() may be different from the value of rs_origin_begin_time.
rs_origin_db varchar(30) Name of the origin database
rs_origin_ds varchar(30) Name of the origin data server
rs_origin_ldb varchar(30) Name of the logical database for a warm standby application
rs_origin_lds varchar(30) Name of the logical data server for a warm standby application
rs_origin_qid varbinary(36) Origin queue ID of the first command in a transaction
rs_origin_user varchar(30) User who executed the transaction at the origin
rs_origin_xact_id binary(120) The system-assigned unique ID of a transaction
rs_origin_xact_name varchar(30) User-assigned name of the transaction at origin
rs_repl_objowner varchar Owner of the replicate object
rs_secondary_qid varbinary(36) Queue ID of a transaction in a subscription materialization or dematerialization queue
rs_last_text_chunk int(4) If the value is 0, this is not the last chunk of text data. If the value is 1, this is the last chunk of text data.
rs_writetext_log int(4) If the value is 0, rs_writetext has not finished logging text, unitext, and image data at the primary database transaction log. If the value is 1, rs_writetext has finished logging text, unitext, and image data at the primary database transaction log.

If you are not using parallel DSI to process large transactions before their commit has been read from the DSI queue, the value of the rs_origin_commit_time system variable contains the time when the last transaction in the transaction group committed at the primary site.

If you are using parallel DSI to process large transactions before their commit has been read from the DSI queue, when the DSI threads start processing one of these transactions, the value of the rs_origin_commit_time system variable is set to the value of the rs_origin_begin_time system variable.

When the commit statement for the transaction is read, the value of rs_origin_commit_time is set to the actual commit time. Therefore, when the configuration parameter dsi_num_large_xact_threads is set to a value greater than zero, the value for rs_origin_commit_time is not reliable for any system function other than rs_commit.

System Variables and NULL Values

  • The following system variables may have NULL values:
    • rs_origin_ds

    • rs_origin_db

    • rs_origin_user

    • rs_origin_xact_name

    • rs_destination_db

    • rs_destination_user

    • rs_dump_dbname

    • rs_dump_label

    When a system variable has no value, Replication Server maps the word “NULL” into function-string templates. This may cause syntax errors in some generated statements. For example, the following command would be generated if rs_origin_xact_name has a null value:
    begin transaction NULL
    To prevent this error, create a function string with an output template like the following:
    'begin transaction t_?rs_origin_xact_name!sys_raw?'

    If the rs_origin_xact_name system variable is null, the transaction name will be “t_NULL”.

Replacing Function Strings

  • To replace a function string, use alter function string or create function string with overwrite. Either approach executes drop function string and create function string in a single transaction, preventing errors that could result from temporarily missing function strings.

Permissions

create function string requires “create object” permission.

Related reference
alter function string
configure connection
create connection
create function string class
create subscription
define subscription
drop function string