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().
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.