Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    14

    Unanswered: FUNCTION STRING Sybase replication server

    Hi friends,

    I want a help on sybase replication server FUNCTION STRING cmd.

    In my source table there are input coming in small case.

    When these rows are replicated at target side these sud be in upper case.

    I have never writen a function string.

    Simple example will do :- say for table like this

    create table test (col1 int, col2 varchar(15))

    col2 to should be in upper case at target side.

    Please help................

  2. #2
    Join Date
    Jan 2009
    Posts
    14
    I have created a Function String like below:
    what sud be changed to get desired output

    create function string def_infitest.rs_insert
    for sqlserver_derived_class
    output language
    'insert INFITEST values
    (?col1!new?,
    ?col2!new?)'
    go

  3. #3
    Join Date
    Jun 2009
    Location
    South Africa
    Posts
    33
    ramjitu,

    You can accomplish the same by using the functionality provided in the Replication Definitions. Function strings are very powerful, and in my support lifetime we have pulled out various fancy feats at clients using function strings. Stuff like adding multiple records from a single insert, transposing wide column info into multiple single rows, using if exists syntax to either insert or update depending on whether records exists or not, masking out deletes.
    There are some drawbacks if you use function strings :
    - You cannot use minimal column replication - send through only primary key cols and changed column info instead of a query containing ALL the information
    - If you have text / image data, you'll have to create function strings for these as well.
    - You'll need to create function strings for a minimum of the rs_insert, rs_update and rs_delete functions.

    You can use the Replication Definition syntax to accomplish the same, while still supporting all the standard features :
    Code:
      create replication definition repdef_name
      with primary table named [owner.]tablename
      with replicate table named [owner.]tablename
      (
        column_name [as replicate_column_name] datatype [map to new_datatype]
        [, column_name [as replicate_column_name] datatype [map to new_datatype] ]
      primary key ( column_name [, additional_key_cols...] )
    So you can change table owner, table name, column name and column datatype via the normal RepDef:
    Code:
    create replication definition EMPLOYEE
    with primary table named dbo.employee
    with replicate table named HR.EMPINFO
    ( 
      emp_id as EMPID int map to numeric,
      emp_firstname as EMPNAME varchar(30),
      emp_lastname as EMPSURNAME varchar(40),
      emp_manager_id as EMPMANAGE int map to numeric
    )
    primary key ( emp_id )
    The above would replicate data from a table named dbo.employee to a replicate table named HR.EMPINFO, renaming the target column names as follow :
    emp_id -> EMPID
    emp_firstname -> EMPNAME
    emp_lastname -> EMPSURNAME
    emp_manager_id -> EMPMANAGE

    and also manage the on-the-fly datatype conversions for integer to numeric for the emp_id and emp_manager_id columns to their respective targets.

    You can have multiple of the same type of replication definitions for a single spurce table, and only create a subscription to the correct repdef for a specific target that has that type of layout / naming convention :

    Code:
    create replication definition ORAEMPLOYEE
    with primary table named dbo.employee
    with replicate table named HR.EMPINFO
    ( 
      emp_id as EMPID int map to numeric,
      emp_firstname as EMPNAME varchar(30),
      emp_lastname as EMPSURNAME varchar(40),
      emp_manager_id as EMPMANAGE int map to numeric
    )
    primary key ( emp_id )
    go
    
    create replication definition DB2EMPLOYEE
    with primary table named dbo.employee
    with replicate table named EMPLOYEE
    ( 
      emp_id as EMPLOYID int map to numeric,
      emp_firstname as EMPFNAME varchar(30),
      emp_lastname as EMPLNAME varchar(40),
      emp_manager_id as EMPMANGR int map to numeric
    )
    primary key ( emp_id )
    go
    For an Oracle target, you can then subscribe to ORAEMPLOYEE and for a DB2 target you can subscribe to DB2EMPLOYEE, requiring no additional fancy coding on your part.

  4. #4
    Join Date
    Jan 2009
    Posts
    14
    Hi Lerac,

    I think u have not got my requirement correctly.

    I require to replicate data from lower case to upper case.

    I want the following

    Source
    Col1 Col2
    1 jitu
    2 lerac

    Target
    Col1 Col2
    1 JITU
    2 LERAC


    Data needs to converted in upper and lower case

    As of now i have done this using following functions string .

    create function string def_infitest.rs_insert
    for rs_oracle_function_class with overwrite
    output language
    'insert into infitest
    (col1,col2)
    values (?col1!new?, upper(?col2!new?))'

    Please Suggest if there is some other better way to do this.
    Can this be done in the replication definition itself .........

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •