create function string

Description

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

Example 1

Creates a function string for the rs_begin function:

create function string rs_begin
 for sqlserver2_function_class
 output language
 'begin transaction'

Example 2

Creates a function string for the rs_commit function that contains two commands separated with a semicolon. The function string executes an Adaptive Server stored procedure that updates the rs_lastcommit system table and then commits the transaction:

create function string rs_commit
 for sqlserver2_function_class
 output language
 'execute sqlrs_update_lastcommit
   @origin = ?rs_origin!sys?,
   @origin_qid = ?rs_origin_qid!sys?,
   @secondary_qid = ?rs_secondary_qid!sys?;
   commit transaction'

Example 3

Examples 3 and 4 create a replication definition for the titles table and an rs_insert function string for the sqlserver2_function_class. The function string inserts data into the titles_rs table instead of into the titles table in the replicate database:

create replication definition titles_rep
 with primary at LDS.pubs2
 (title_id varchar(6), title varchar(80),
  type char(12), pub_id char(4), advance money,
  total_sales int, notes varchar(200),
  pubdate datetime, contract bit, price money)
 primary key (title_id)
 searchable columns (price)

Example 4

Examples 3 and 4 create a replication definition for the titles table and an rs_insert function string for the sqlserver2_function_class. The function string inserts data into the titles_rs table instead of into the titles table in the replicate database:

create function string titles_rep.rs_insert
 for sqlserver2_function_class
 output language
 'insert titles_rs values (?title_id!new?,
   ?title!new?, ?type!new?, ?pub_id!new?,
   ?advance!new?, ?total_sales!new?, ?notes!new?,
   ?pubdate!new?, ?contract!new?, ?price!new?)'

Example 5

Examples 5 and 6 create a user-defined function update_titles and a corresponding function string for the sqlserver2_function_class. The function string executes an Adaptive Server stored procedure named update_titles:

create function titles_rep.update_titles
 (@title_id varchar(6), title varchar(80),
  @price money)

Example 6

Examples 5 and 6 create a user-defined function update_titles and a corresponding function string for the sqlserver2_function_class. The function string executes an Adaptive Server stored procedure named update_titles:

create function string titles_rep.update_titles
 for sqlserver2_function_class
 output rpc
 'execute update_titles
   @title_id = ?title_id!param?,
   @title = ?title!param?,
   @price = ?price!param?'

Example 7

The rs_select function string in example 7 is used to materialize subscriptions that request rows with a specified value in the title_id column. Similar to example 8, the input templates given by the scan clauses differentiate the two function strings:

create function string
   titles_rep.rs_select;title_id_select
 for sqlserver2_function_class
 scan 'select * from titles
   where title_id = ?title_id!user?'
 output language
 'select * from titles
   where title_id = ?title_id!user?'

Example 8

The rs_select function string in example 8 is an example of an RPC function string. It is used to materialize subscriptions that request rows where the value of the price column falls within a given range:

create function string
   titles_rep.rs_select;price_range_select
 for sqlserver2_function_class
 scan 'select * from titles
   where price > ?price_min!user?
   and price < ?price_max!user?'
 output rpc
   'execute titles_price_select
   ?price_min!user?, ?price_max!user?'

Usage

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

• 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 semi-colon within data of character datatypes.

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

The following table lists system-defined variables that can be used in function-string output templates. Use the sys or sys_raw modifier for these variables:

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.

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

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

See also