AS400 EBCDIC 870 code page to Win1250 on SQL Server 2005

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


The problem is that SQL Server has no native support for the EBCDIC 870 code page.

If you use

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?

more ▼

asked Dec 17, 2009 at 08:15 AM in Default

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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?

more ▼

answered Dec 18, 2009 at 07:34 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Dec 18, 2009 at 05:42 AM

avatar image

10.9k 27 37 37

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 17, 2009 at 08:15 AM

Seen: 10816 times

Last Updated: Dec 17, 2009 at 08:46 AM

Copyright 2018 Redgate Software. Privacy Policy