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 "Bär"

- SELECT bar FROM foo gives me the Result "Bär"
- SELECT bar FROM foo WHERE bar like 'B%' gives me the Result "Bär"
- 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

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?

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.

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

I would run such a SELECT statement:
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...

"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.

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

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
Which environment variable is used by DBVisualizer, I do not know.

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...