create function string

Syntax

create function string
        {replication_definition |
        [owner.] table | 
        stored_procedure} .function[;function_string]
        for { [function_class] function_class |
        [database] data_server.database}
        [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

• replication_definition – The name of the replication definition the function operates on. Use only for functions with replication definition scope.

  Functions have function-string-class scope, replication definition scope, or target scope.

  Functions that direct transaction control have function-string-class scope. User-defined functions, and functions that modify data and created against replication definitions have replication definition scope.

  Function strings created against standby or replicate tables or stored procedures are target scope function strings.

• [owner.]table – specifies the target table and the table owner of the function string.
• stored procedure – specifies the target store procedure of the function string
• function – The name of the function. Names for system functions must be provided as documented in Replication Server System Functions. Names for user-defined functions must match an existing user-defined function.
• function_string – A function string name is required when customizing rs_get_textptr, rs_textptr_init, and rs_writetext functions, and optional for others. For rs_get_textptr, rs_textptr_init, and rs_writetext, a function string is needed for each text, unitext, or image column in the replication definition. The function string name supplied must be:

  • The text, unitext, or image column name for the replication definition.

  • Able to conform to the rules for identifiers.

  • Unique in the scope of the function.

  Replication Server also uses the function string name in the generation of error messages.

• function_class – specifies the function class the function string is associated with for replication definition-scope function strings.
• data_server.database – specifies the standby or replicate database where you want to create a target-scope function string for target table or stored procedures.

  See Create Function Strings in the Replication Server Administration Guide Volume 2.

• with overwrite – If the function string already exists, this option drops and re-creates the function string, as though you used alter function string instead. You can use the with overwrite option only with create function string.
• scan – Precedes an input template.
• input_template – A character string, enclosed in single quote characters, that Replication Server scans to associate an rs_select or rs_select_with_lock function string with the where clause in a create subscription command. An input template string is written as a SQL select statement, with user-defined variables instead of the literal values in the subscription’s where clause.
• output – Precedes an output template.
• language – Tells Replication Server to submit the output template commands to the data server using the Client/Server Interfaces language interface.
• lang_output_template – A character string, enclosed in single quote characters, that contains instructions for a data server. A language output template string may contain embedded variables, which Replication Server replaces with run-time values before it sends the string to the data server.
• rpc – An output template that tells Replication Server to use the Client/Server Interfaces remote procedure call (RPC) interface. Replication Server interprets the string and constructs a remote procedure call to send to the data server.

  These keywords and options appear in RPC output templates:

  procedure – the name of the remote procedure to execute. It could be an Adaptive Server stored procedure, a procedure processed by an Open Server gateway RPC handler, or a registered procedure in an Open Server gateway. Refer to the Open Server Server-Library/C Reference Manual for information about processing RPCs in a gateway program.

  @param_name – the name of an argument to the procedure, as defined by the procedure. If the @param_name = value form is used, parameters can be supplied in any order. If parameter names are omitted, parameter values must be supplied in the order defined in the remote procedure.

  constant – a literal value with the datatype of the parameter it is assigned to.

  ?variable!mod? – variable is the placeholder for a run-time value. It can be a column name, the name of a system-defined variable, the name of a parameter in a user-defined function, or the name of a variable defined in an input template. The variable must refer to a value with the same datatype as the parameter it is assigned to. For a list of system-defined variables, see System-Defined Variables.

  The mod portion of a variable name identifies the type of data the variable represents. The variable modifier is required for all variables and must be one of the following:

  Function String Variable Modifiers

  Modifier	Description
  new, new_raw	A reference to the new value of a column in a row you are inserting or updating
  old, old_raw	A reference to the existing value of a column in a row you are updating or deleting
  user, user_raw	A reference to a variable that is defined in the input template of an rs_select or rs_select_with_lock function string
  sys, sys_raw	A reference to a system-defined variable
  param, param_raw	A reference to a function parameter
  text_status	A reference to the text_status value for text, unitext, or image data. Possible values are: 0x000 – Text field contains NULL value, and the text pointer has not been initialized. 0x0002 – Text pointer is initialized. 0x0004 – Real text data will follow. 0x0008 – No text data will follow because the text data is not replicated. 0x0010 – The text data is not replicated but it contains NULL values.

  Note: Function strings for user-defined functions may not use the new or old modifiers.
• writetext – instructs Replication Server to use the Client-Library™ function ct_send_data to update a text, unitext, or image column value. This option applies only to the rs_writetext function.

  The following options appear in writetext output templates to specify the logging behavior of the text, unitext, or image column in the replicate database:

  use primary log – logs the data in the replicate database, if the logging option was specified in the primary database.

  with log – logs the data in the replicate database transaction log.

  no log – does not log the data in the replicate database transaction log.

• none – applies to all functions, and provides the flexibility to identify which function-strings Replication Server can avoid executing on replicate databases:

  • For rs_writetext functions – instructs Replication Server not to replicate a text, unitext, or image column value.

  • For non-rs_writetext functions – instructs Replication Server not to execute commands on the replicate database.

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?'

• Example 9 – Creates a target-scope function string for the upd_datetime stored procedure for database NY_DS.rdb1:

  create function string upd_datetime.upd_datetime
       for database NY_DS.rdb1
       with overwrite
            output language
            'update datetime set
            row_num = ?row_num!param?,
            datecol = ?datecol!param?,
            timecol = ?timecol!param?,
            ndatecol = ?ndatecol!param?,
            ntimecol = ?ntimecol!param?,
            comment = ?comment!param?
            where
            row_num = ?row_num!param?'

• Example 10 – Creates a target-scope function string for the dbo.datetime table for NY_DS.rdb1:

  create function string dbo.datetime.rs_insert
       for database NY_DS.rdb1
       with overwrite
       output language
       'insert datetime values (
               ?row_num!new? ,
               ?datecol!new? ,
               ?timecol!new? ,
               ?ndatecol!new? ,
               ?ntimecol!new? ,
               ?comment!new?)
         update fn_monitor set insert_count = insert_count + 1'

• Example 11 – Creates the rs_writetext customized function string for the dbo.tbl1.unitext_fd1 column:

  create function string dbo.tbl1.rs_writetext; unitext_fld1 for NY_DS.rdb1
           output RPC
          'exec update_repl_unitext
                  @p_key       = ?p_key!new?,
                  @unitext_fld       = ?unitext_fld1!new?,
                  @last_chunk         = ?rs_last_text_chunk!sys?'

• Example 12 – Creates a target-scope function string for the dbo.tbl1 table:

  create function string dbo.tbl1.rs_datarow_for_writetext
  for NY_DS.rdb1
          output RPC
          'exec update_txtimg_stat
                  @p_key       = ?p_key!new?,
                  @txtfld_stat = ?unitext_fld1!text_status?'

• Example 13 – Creates the rs_insert customized function string for the dbo.authors table at the rdb1 target database in the NY_DS data server:

  create function string dbo.authors.rs_insert
       for database NY_DS.rdb1
       output language
       'insert authors values (
             ?au_id!new? ,
             ?au_lname!new? ,
             ?au_fname!new? ,
             ?phone!new? , 
             ?address!new? , 
             ?city!new? , 
             ?state!new? ,
             "00000" , 
             ?contract!new?)
        update fn_monitor set insert_count = insert_count + 1' 

• Example 14 – Creates a customized function string for the upd_bits stored procedure at the rdb1 target database in the NY_DS data server. Functions for a stored procedure have the same name as the stored procedure:

  create function string upd_bits.upd_bits
       for database NY_DS.rdb1
       with overwrite
       output language
       'exec upd_bits
             @firstbit = ?firstbit!param?,
             @secondbit = ?secondbit!param?, 
       @commit = ?comment!param?'

Usage

Function Strings and Function-String Classes

Function Strings and Replicate Minimal Columns

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.

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

Replacing Function Strings

Permissions

create function string requires “create object” permission.