I have a linked server to DB2 database on AS400 machine. The data in the db are encoded using EBCDIC 870 code page and contain characters like Ž (hex B8 in EBCDIC), Ř (hex AE).
The SQL server uses Win1250 code page and I need to receive a correct characters, when querying data from the linked server by OPENQUERY like for example
SELECT * FROM OPENQUERY(AS400, 'SELECT * FROM SSCUSTP')
The problem is that SQL Server has no native support for the EBCDIC 870 code page.
If you use
SELECT * FROM fn_helpcollations()
You can see a few EBCDIC collation, but not 870.
When simply calling the above mentioned select to retrieve data, I receive wrong characters for non ASCII characters. What more, in the result more EBCDIC characters are converted tot he same Win1250 characters.
We are using IBM DB2 ODBC driver for the Linked Server.
Any help for this?
Answer by Pavel Pawlowski ·
The discussion on SSC mentioned do not directly resolve my problem..
The discussion is related to SSIS, but could be possibly applicable out of SSI.
They retrieve raw data and locate fields in them.
I need to use Live data using the SELECT statements in the OPENQUERY.
AS I wrote in the question, I was googling and binging about this a lot and didn't found any suitable solutin.
Finaly.. I found solution by myself using HEX function of DB2 and CRL scalar function (or T-SQL scalar function with Convertion table) on the SQL Server side which allows convertion of fields I want :-)
I have a description of this solution on my site here
The CRL function mentioned on my site allows encoding from EBCDIC 870 to any encoding you want. Generally after slight modification it can convert any source encoding to any destination encoding
Maybe some other people will find this solution as suitable for such conversions. It's simple and quick to implement.
Or better, does anybody know about a better solution?
Answer by sp_lock ·
Maybe the SSc post will help?