Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2010
    Posts
    6

    Unanswered: Problem with umlauts in where-clause

    Hello all,

    I have this problem with german umlauts in where clauses. Let's say I have a table "of" with a column "bar" and one row which is "Br"

    - SELECT bar FROM foo gives me the Result "Br"
    - SELECT bar FROM foo WHERE bar like 'B%' gives me the Result "Br"
    - SELECT bar FROM foo WHERE bar like 'B%' gives me nothing back

    I use DB2 9.5.4 on AIX64, DB has Codepage 819, territory DE and Codeset ISO8859-1

    Any idea?

    Thanks in advance,
    Michael

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It probably has more to do with your client and its locale, than with the WHERE clause.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Mar 2010
    Posts
    6
    Quote Originally Posted by n_i View Post
    It probably has more to do with your client and its locale, than with the WHERE clause.
    I tried a lot there but no success. Any recommendations?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by mclemens View Post
    I tried a lot there but no success. Any recommendations?
    What did you try?

    For the third case, can you check what is on the db server? get a dynamic sql snapshot and lookup if the query has been passed as desired.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2010
    Posts
    6
    On client side, I tried different combinations of $DB2CODEPAGE (819, 1208) and $LANG (en_US, de_DE, de_DE.UTF-8)

    I also tried a dynamic sql snapshot as you suggested and have seeb the umlauts displayed correctly in there.

    Michael

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would run such a SELECT statement:
    Code:
    SELECT HEX('Bär') FROM sysibm.sysdummy1
    Thus, you can see exactly which characters arrive at the DB2 side. You can double-check the code points, represented as hex numbers and make sure that indeed an 'ä' is received. You'll have to verify the code point in the code page of the DB2 server. If you don't find the correct character (which most of us here suspect), you have some conversion problem between your application and DB2.

    If your application uses CLI/ODBC for the communication, you can also gather a CLI trace and verify in the trace which data is being transferred.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Mar 2010
    Posts
    6
    Quote Originally Posted by stolze View Post
    I would run such a SELECT statement:
    Code:
    SELECT HEX('Bär') FROM sysibm.sysdummy1
    Thus, you can see exactly which characters arrive at the DB2 side. You can double-check the code points, represented as hex numbers and make sure that indeed an 'ä' is received. You'll have to verify the code point in the code page of the DB2 server. If you don't find the correct character (which most of us here suspect), you have some conversion problem between your application and DB2.

    If your application uses CLI/ODBC for the communication, you can also gather a CLI trace and verify in the trace which data is being transferred.
    The query gives me back: 42C3A472
    Honestly I don't know how to proceed from here on...
    Last edited by mclemens; 03-29-10 at 07:52.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    "man ascii" on a decent Unix system shows that 0x42 is the code point for 'B' and 0x72 for 'r'. Thus, 0xC3A4 should be a UTF8 code point for 'ä'. You may want to double-check that this is correct for the code page that your database server is running on.

    Also, you can apply the HEX() function to the data in your table. It should also return 0xC3A4 for all 'ä's.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Mar 2010
    Posts
    6
    After switching character encoding of my Gnome Terminal from UTF-8 to ISO-8859-1 qeuerying works now. The same query in an GUI application like DBVisualizer oder db2cc does not work.

    Executing SELECT HEX('') FROM sysibm.sysdummy1 in Terminal (where everything's ok now) gives me E4 back.
    Executing SELECT HEX('') FROM sysibm.sysdummy1 in db2cc gives me C3A4 back.

    So the in my database must be saved as E4, right? What should I do now?

    Thanks,
    Michael

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You'll have to figure out how to set the proper code page for "db2cc" and "DBVisualizer". A quick search turned up this: DB2 Control Center Code Page | db2ude
    Code:
    $ export DB2CODEPAGE=1208
    $ db2cc
    Which environment variable is used by DBVisualizer, I do not know.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Mar 2010
    Posts
    6
    Setting DB2CODEPAGE for db2cc doesn't work for me I still can't send umlauts correctly.

    What works: select * from foo where hex(bar) like '42E4%'

    But when typing '', a C3A4 will be sent. I tried with db2cc and DBVisualizer under Windows and Linux on different Workstations. It makes me mad...

Posting Permissions

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