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