DB2: How to set encoding for db2clp under Windows?

3
votes

I have a DB2 that was created with encoding set to UTF-8

db2 create database mydb using codeset UTF-8

My data insert scripts are also stored in encoding UTF-8. The problem now is that the command line processor seems to work with a different encoding as the Windows installation doesn't use UTF-8:

C:\Users\Administrator>chcp
Active code page: 850

This leads to the problem that my data (which contains special characters) is not stored correctly to the database.

Under Linux/AIX I could change the command line encoding by setting

export LC_ALL=en_US.UTF-8

How do I achieve this under Windows? I already tried

chcp 65001

UPDATE: But that won't have any effect? It seems like the db2clp can't deal with the UTF-8 encoded file because it will print out junk:

D:\Program Files\ibm_db2\SQLLIB\BIN>chcp 65001
Active code page: 65001

D:\Program Files\ibm_db2\SQLLIB\BIN>type d:\tmp\encoding.sql
INSERT INTO MY_TABLE (ID, TXT) VALUES (99, 'äöü');


D:\Program Files\ibm_db2\SQLLIB\BIN>db2 connect to mydb

Datenbankverbindungsinformationen

Datenbank-Server                                                = DB2/NT64 9.5.0
SQL-Berechtigungs-ID                                            = MYUSER
Aliasname der lokalen Datenbank                                 = MYDB

D:\Program Files\ibm_db2\SQLLIB\BIN>db2 -tvf d:\tmp\encoding.sql
INSERT INTO MY_TABLE (ID, TXT) VALUES (99, 'äöü')
DB20000I  Der Befehl SQL wurde erfolgreich ausgeführt.
2
windowsencodingdb2
it looks like, the first character (the one before DELETE) of encloding.sql is malformed? – Peter Miehle
Yes, it looks that way. But I already removed my file, created it from scratch, removed every content in the file but still the db2 cmd line tool cannot deal with the UTF-8 encoded file. – user1613270

2 Answers

4
votes

You need to set both:

CHCP 65001
SET DB2CODEPAGE=1208

on the db2cmd command line, before running db2 -tvf. This works for databases that have CODESET set to UTF-8. To check the CODESET setup for database run:

db2 get db cfg for <your database>

and look for "Database code page" and "Database code set" they should be 1208 and UTF-8 respectively.

1
votes

when dealing with encodings, you have to take a careful look into your envirnoments, and where you are currently. So in your case:

  • the Server stores its data in encoding A (like UTF-8)
  • the client resides in an environment which has encoding B (like windows-1252)

in your client, you have to have to use the encoding of your client (or tell the client you intentionally use another encoding on client side (like UTF-8-encoded file inside a windows-1251 environment)!). The connection between the Client and the server is doing the work for you to change encoding B into encoding A for storing the data into the database.