Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    14

    Unanswered: Find Replace substrings in DB2 columns

    Hi,
    I have a set of Search/Replace word pairs that I store in a DB2 master Table.
    I have transaction data in another db2 table.
    I need to search and replace the word pairs in the transaction data.

    Am currently achieving this using a stored proc that gets the master list into a cursor, and then iteratively calls an update with a Replace Function.

    The performance is pretty pretty poor in comparison to doing the same in excel.

    What are the high-performance ways of doing this?
    TIA
    Kar

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You probably have not gotten any responses because your question is to vague. You need to be more specific what you are trying to do. Give examples if necessary. The more information you supply the better the response you will get. Also supply the DB2 version and OS.

    Andy

  3. #3
    Join Date
    Jun 2008
    Posts
    14
    I have masterTable with two cols, badValue and GoodValue. Lets say it has n rows with values (badvalue1, Goodvalue1),(badvalue2, Goodvalue2) and so on.
    I have TranTable with 100 cols, col1, col2 ....., col100.
    I call the Replace function 100 x n times, to replace badvalue(n) with goodvalue(n) in each column.

    Would have been a cinch in text, but in a DB, it is a real performance killer.

    Please let me know if you want me to be more detailed.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I assume that if any columns in you transtable does not have a badvalue it is left alone.

    Then instead of using a cursor which has too many round trips (1 for each row) to the DB, try doing it in a single statement. Since you have 100 columns it will not be pretty. Something like:

    update transtable set (col1,col2,col3,...,col100) =
    (coalesce(mt1.goodvalue,col1),coalesce(mt2.goodval ue,col2,coalesce(mt3.goodvalue,col3,...,coalesce(m t100.goodvalue,col100))
    from transtable
    left outer join mastertable as mt1 on (col1 = mt1.badvalue)
    left outer join mastertable as mt2 on (col2 = mt2.badvalue)
    left outer join mastertable as mt3 on (col3 = mt3.badvalue)
    .
    .
    .
    left outer join mastertable as mt100 on (col100 = mt100.badvalue)

    HTH

    Andy

Posting Permissions

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