-1

Once I connect to the database (DB2) to check the values in the tables, if they have special chars then I see their utf-8 text value:

enter image description here I expected instead to see the correct: Tükörfúrógép.

I am still able to handle the value properly, but is there any configuration in the db that I am missing to display the value properly when checking the table?

More Info:

Connected to DB with Intellij and also tried with DbVisualizer.

The following JDBC connection was used in intellij:

jdbc:db2://(...)?characterEncoding=UTF-8;

Tried both with the characterEncoding and without getting the same results.

I am still able to handle the value properly, but is there any configuration in the db that I am missing to display the value properly when checking the table?

DB Version: v11 LUW

JDBC: com.ibm.db2.jcc -- db2jcc4 -- Version 10.5

Encoding being used: UTF-8

db2 "select char(value,10), char(name,10) from sysibmadm.dbcfg where name like 'code%'"

1 2
---------- ---------- 1208 codepage UTF-8 codeset

2 record(s) selected.

UPDATE 1:

  • I was able to directly insert in the database values with special chars, so starting to think this is not DB2 configuration missing but maybe jdbc or other related issue.
André Ramos
  • 1
  • 2
  • 1
    How do you connect, which tool, which config for char encoding, what is the database encoding? It is a matter of configuration and conversion – data_henrik Jun 07 '21 at 14:26
  • Thanks for the feedback data_henrik, updated the question with the tools used. Trying to confirm the database encoding. Most of db2 documentation is broken... – André Ramos Jun 07 '21 at 14:55
  • please edit your question to show the output of the following query: `select char(value,10), char(name,10) from sysibmadm.dbcfg where name like 'code%'` , and if you lack permission then ask your DBA or someone with relevant permissions. – mao Jun 07 '21 at 17:48
  • What's the result of `select hex(town) from ...`? – Mark Barinstein Jun 07 '21 at 18:12
  • Thanks @mao, the encoding being used in the database is UTF-8. – André Ramos Jun 08 '21 at 08:40
  • @MarkBaristein the result is 54C383C2BC6BC383C2B67266C383C2BA72C383C2B367C383C2A970 – André Ramos Jun 08 '21 at 08:40
  • In my dbvis (v12.0.4) on linux, with Db2 v11.5.5 the correct characters appear for that UTF-8 string, and I get the same hex code as you. I do not have any property for characterEncoding in the connection string. – mao Jun 08 '21 at 09:20
  • I see, thanks for the extra info @mao. Using here dbvis(v10.0.21) and db2(v11.1.0), but in dbvis in the properties of the database which enconding do you have selected? – André Ramos Jun 08 '21 at 10:04

1 Answers1

0

You must have the following HEX string representation for given string Tükörfúrógép in UTF-8 database:
54C3BC6BC3B67266C3BA72C3B367C3A970.
But you have the following instead with repeating garbage symbols:
54C383C2BC6BC383C2B67266C383C2BA72C383C2B367C383C2A970

You may try to manually remove such a byte sequence with the following statement, but it's better to understand a root cause of such a garbage appearance in this column.

VALUES REPLACE (x'54C383C2BC6BC383C2B67266C383C2BA72C383C2B367C383C2A970', x'83C2', '');
SELECT REPLACE (TOWN, x'83C2', '') FROM ...;
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
  • That's a great point, thanks @MarkBaristein, looking into it. Also I inserted manually some values with specials chars directly in the database and in that case it is correctly stored. Might be some jdbc or other thing adding the "garbage" – André Ramos Jun 08 '21 at 10:46