Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: Transforming data while replicating

    Hello,

    I have an issue with DB2 replication that I don't know how to manage. I'll try to describe the situation first:
    I have multiple locations with the same database structure. In each location I have two tables of intrest (for now): EMPLOYEE (EMP_ID, EMP_NAME, ...) and EMPLOYEE_REG (EMP_ID, EMP_CODE) where EMP_ID is an internal id (location dependent) and EMP_CODE is an external code that is unique for all locations. From those locations I want to "collect" employee data in one central db used for reporting that has almost the same structure with all other databases, using DB2 Replication Center - SQL Replication. (EMPLOYEE table will have EMP_CODE, EMP_NAME, ... fields)

    I've managed to replicate the data from one location to central location using the same stucture for the target table as the source.
    Now my problem is that I can't leave EMP_ID as id for target db because is not unique.
    I must transform my collected data before transferring it to central db. (transform EMP_ID into EMP_CODE)
    I've searched the web for a solution and found out that are multiple ways in how to transform db data (for ex.: Transforming Data as You Replicate)

    I successfully transformed my EMP_ID data using a view that I've register as source view in Replication Center. The drawback for this solution is that a view must be written for each transformation, for each table, for each db location and this is not ok for me.

    I've made a function that transforms my EMP_ID into EMP_CODE and tried using it in column mapping (at column level expressions) but db2 replication center doesn't let using user defined functions (i think).

    Another way is by using stored procedures but I don't know what my procedure should do and how to "link" the result from the procedure with the replication center. (I didn't find examples or tutorials on how to do this, and this is rather a problem of how replication center and stored procedures work together)

    I don't want to use other solutions like a transformation engine or data capture technologies. My solution must be based only on db2 replication center.

    So... my questions are:
    1. Why can't I use user defined functions when mapping field? Is there a way that I could "force" db2 RC to use my functions?
    2. What should my stored procedure do to transform my data while replicating? On what data my procedure should operate: on original table (EMPLOYEE), on change-table data (CDEMPLOYEE), on the target table (TGEMPLOYEE) ? Could you give me an example?

    Thank you and sorry for the long post.

  2. #2
    Join Date
    Jun 2012
    Posts
    2
    Quote Originally Posted by retsam4u View Post
    ...
    I've made a function that transforms my EMP_ID into EMP_CODE and tried using it in column mapping (at column level expressions) but db2 replication center doesn't let using user defined functions (i think).
    ...
    So... my questions are:
    1. Why can't I use user defined functions when mapping field? Is there a way that I could "force" db2 RC to use my functions?
    ...
    I finally succeeded on using my function. Indeed you cannot use UDFs in replication center own expression editor but you can force it to use UDFs by manually placing the function name with defined parameters in the expression editbox or in the generated script (there are some inserts that map columns). So it can be used any expresion (with any functions) that are recognized by the capturing server. (the functions must be defined on capturing servers)

    Therefore, in the big picture, my problem is solved. I can use my functions everywhere I need a quick transformation.

    The problem remains if the transformation is complex or/and it's hard to accomplish with views or udfs. So I would like to find out how stored procedures can solved this problem.

    Thus, question 2 is still opened!

    Thanks for any suggestions and ideas and hope that this post will help others with same problems as me.

Posting Permissions

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