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

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

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.

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