Results 1 to 7 of 7

Thread: sql0176n

  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368

    Unanswered: sql0176n

    v9.7 FP7; AIX


    UPDATE aaa.bbb
    SET CONTACT_NAME = translate (CONTACT_NAME, 'AACEEEEIIOUU',''),
    CONTACT_DEPT = translate (CONTACT_DEPT, 'AACEEEEIIOUU','')
    WHERE BKR_NUM='x11'

    returns sql0176n


    Could this error be related to collating sequence?


    DB where this statement works:

    Code:
     Database territory                                      = US
     Database code page                                      = 819
     Database code set                                       = ISO8859-1
     Database country/region code                            = 1
     Database collating sequence                             = IDENTITY
     Alternate collating sequence              (ALT_COLLATE) =

    DB where this statement doesn't work:

    Code:
     Database territory                                      = US
     Database code page                                      = 1208
     Database code set                                       = UTF-8
     Database country/region code                            = 1
     Database collating sequence                             = SYSTEM_819
     Alternate collating sequence              (ALT_COLLATE) =

    If this is related to collating sequence, how can it be fixed without rebuilding the db? Use REPLACE instead of TRANSLATE or something else?

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    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 ...

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Thanks, db2mor.

    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?

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    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.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Try this:

    Code:
    create function translate_utf (
      str  varchar(4000)
    , to   varchar(4000)
    , from varchar(4000)
    )
    returns varchar(4000)
    contains sql
    deterministic
    no external action
    return 
    xmlcast(
    xmlquery('fn:translate($s, $o, $r)' passing 
      str  as "s"
    , from as "o"
    , to   as "r"
    )
    as varchar(4000))
    Regards,
    Mark.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Thanks | Спасибо.

  7. #7
    Join Date
    Oct 2003
    Location
    Curitiba - PR - Brazil
    Posts
    18
    Mark, thank you very much, it helped me a lot.

Posting Permissions

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