It is the code set - the UTF-8 is a variable length encoding, and
TRANSLATE wants the replacement(s) to have identical byte length as the original byte-length.
The ISO8859 is a fixed length encoding, so that's why your TRANSLATE works in that encoding.
What's the point of having a utf-8 encoded database if you want to remove accented characters? Some customers (natural persons or organizations) dislike computer-systems that change the spelling of the names, because changing-names can have legal and financial implications ...
The non-working db was incorrectly created (took defaults when creating the db). As per my understanding, developers use TRANSLATE to translate French characters (?). Is there some way to temporary fix the error without rebuilding the db? If the db is not rebuilt, what else could be impacted?
I've already mentioned that it seems functionally dubious for database code to change data by replacing accented characters by non-accented ones.
I believe it's the job of presentation layer code to deal with accented characters properly - an architectural principle perhaps.
If your application expects a fixed-width encoding then it's best to remake the database and ensure that data movement performs the code-page conversion properly.
If you can prove that only a single-table can contain data with accented characters (although unlikely) then you can try creating a new table with CCSID ASCII and copy the data from old to new, although this qualifies as hacking...
It's just better to handle accented characters correctly...without altering them.
create function translate_utf (
, to varchar(4000)
, from varchar(4000)
no external action
xmlquery('fn:translate($s, $o, $r)' passing
str as "s"
, from as "o"
, to as "r"