Getting problem with Chinese characters when loading to DB2

ray.wurlod · Post by **ray.wurlod** » Sun Mar 13, 2011 11:46 pm

Probably not using UTF-8 (though you may be lucky). Try using the appropriate Chinese character map (for example GB2312 or BIG5).

ICE · Post by **ICE** » Mon Mar 14, 2011 2:21 am

Dear Ray,

I have already been tested with those Mapping BIG5 and GB2312 but still cannot populate the right character

I also change the collate but didn't work.
My Input file type is UTF8 format.
Is there any other way please???

Thank you so much for your support.

ray.wurlod wrote:Probably not using UTF-8 (though you may be lucky). Try using the appropriate Chinese character map (for example GB2312 or BIG5). ...

ray.wurlod · Post by **ray.wurlod** » Mon Mar 14, 2011 2:33 am

Can you View Data successfully with respect to your source file?

MT · Post by MT » Mon Mar 14, 2011 2:35 am

Hi ICE,

UTF-8 characters are not simple to handle because in your case you do not know wether the character is incorrectly stored in the database or only displayed in an incorrect way.
Here are some points to consider:

- Do you have a UNICODE font installed to be able to show all the chinese characters?
- Try setting the environment variable DB2CODEPAGE = 1208 in your datastage job
- Check trhe real content of your DB2 datrabase by using hex(col) and compare it to the Unicode codepoint of the character you expect (you have to lookup these in the internet)
- Be aware of the fact that DataStage is able to handle UTF-8 but is NOT able to display it correctly if you do a view on the DB2 API stage for example
- Be aware of the fact that if you select the UTF-8 data from your database with the DB2 GUI you see different results if you use the GUI-Java result tab in db2ce compared to the DB2 command line due to other codepages

ICE · Post by **ICE** » Mon Mar 14, 2011 8:59 pm

Dear Ray,

Yes. I can view it from my UTF-8 text file but I cannot view it from my DS job viewer and also my DS job can output it to text file and can read that file using text editor but getting problem with database

Is there any other idea please???

Thank u so much for your support,

ICE · Post by **ICE** » Mon Mar 14, 2011 9:24 pm

Dear MT,

I checked it from my BI report which enabled for Unicode character such as Chinese characters. The records that I loaded from datastage jobs were shown incorrect character while my report shows correct chinese characters which I directly inserted to my DB2 table.

I have some questions for those options that you advised here.
1. Do you mean that our DS server should have install Chinese font??? Actually I tried to add one Chinese font from Windows font installer but it cannot show the font name even. I do not know why

2. May I know where can I find DB2CODEPAGE variable??? Actually my database is already 1208, UTF-8. It is a Unicode database.
3. I checked it from my BI report and it should show the right character if I could managed to save correctly in my DB2. Right?
4. Yes. You are right. I cannot view from DS viewers.
5. Yes. I cannot read those Chinese characters using DB2 command line.

Thank you so much for your support.

MT · Post by MT » Tue Mar 15, 2011 12:10 am

Hi ICE,

ICE wrote: 1. Do you mean that our DS server should have install Chinese font??? Actually I tried to add one Chinese font from Windows font installer but it cannot show the font name even. I do not know why

=> No I think DS is not able to show the characters in general - I do not think this is a way to a solution

ICE wrote: 2. May I know where can I find DB2CODEPAGE variable??? Actually my database is already 1208, UTF-8. It is a Unicode database.

=> well this is a setting on DataStage level and we got advise from IBM support I think to use it - we I handled Unicode characters in DS 7.5.x.
So it is really worth a try - all our unicode jobs had this variable set the way I described it - as a local job parameter.

ICE wrote: 3. I checked it from my BI report and it should show the right character if I could managed to save correctly in my DB2. Right?

=> Yes.
So if I understood you right - the report shows the correct characters if you run it on the "source" but not if you run it on the "target" DB2.

Here is link that might be useful as well:

http://www.ibm.com/developerworks/data/ ... 10lau.html

http://www.ibm.com/developerworks/data/ ... 10lau.html

With these links you should be able to check your DB2 content directly within DB2 - this should ease your problem determinations a lot.

I hope this helps - codepage problems are always a pain....

ICE · Post by **ICE** » Tue Mar 15, 2011 12:50 am

Dear MT,

Thank you so much for support.
I would go and read your link and try again. Hopefully this can help me.

Really many Thanks

ICE · Post by **ICE** » Tue Mar 15, 2011 1:28 am

Dear MT,

The problem has been solved after I setup the DB2CODEPAGE variable in DS server administrator.
You help me a lot.
I really really many many thanks for your support.

Now I feel so relieffffff.........

Thank you so muchhhhh..... Thanks a lotttttttt

MT · Post by MT » Tue Mar 15, 2011 2:00 am

Hi ICE,

you are welcome!
I am happy that you solved your problem