How to convert character set to unicode in db2 query

0
votes

Server: IBM i Series AS/400 running DB2

Client: Linux using unixodbc

I have a table in a DB2 database with a column of data using CCSID 836 (Simplified Chinese EBCDIC). I want to get results in UTF-16 so they work on other systems, but I'm having a hard time finding the right way to convert.

When I try:

SELECT CAST(MYCOLNAME AS VARCHAR(100) CCSID 13491) FROM MY.TABLE

I get the error:

SQL State: 22522
Vendor Code: -189
Message: [SQL0189] Coded Character Set Identifier 13491 not valid. Cause . . . . . :  Coded Character Set Identifier (CCSID) 13491 is not valid for one of the following reasons: -- The CCSID is not EBCDIC. -- The CCSID is not supported by the system. -- The CCSID is not vaid for the data type. -- If the CCSID is specified for graphic data, then the CCSID must be a DBCS CCSID. -- If the CCSID is specified for UCS-2 or UTF-16 data, then the CCSID must be a UCS-2 or UTF-16 CCSID. -- If the CCSID is specified for XML data, then the CCSID must be SBCS or Unicode. It must not be DBCS or 65545.

How can I convert the data from CCSID 836 into UTF-16? I've been equally unsuccessful with UNICODE_STR().

1
unicodedb2ebcdic
Please edit your question to state Which DB2-client name and version/fixpack is installed on Linux, along with the linux distro name and release. Please also specify which locales you have installed on Linux (output of locale -a) and which is the effective one for unixodbc (value of $LANG) . - mao

1 Answers

2
votes

I can't explain why, but here's what works:

SELECT CAST(MYCOLNAME AS VARCHAR(100) CCSID 935) FROM MY.TABLE

The native CCSID for the column in question is 836, which seems very similar to 935, so I don't understand the difference. But 935 works for me.