AS/400 data type mismatch during metadata import

[anilkona]

We are trying to import some data from AS/400 system. We setup ODBC drivers using IBMrios-odbc-5.0.050414.

When we import metadata on a table that has column of type 'char', Ascential sees it as 'binary'. And when we try to view data from the ODBC stage, it displays the data as '453244344532', '443534303430' etc.

When we specify a selection criteria in the ODBC stage like FIELD_NAME='C', then it seems to be showing only rows with value of 'C'.

But then we use that particular field to lookup or join with a char type of field in different database, it tries to match the numbers '443534303430' etc.

Can I use conversion routines like OConv or IConv?
Did any one run into issues like this?

Thanks for your time and help,
-Anil

[yannish]

As I tumbled myself recently into this same problem I'll post my solution here.

In AS400 data can be stored in CHAR() FOR BIT DATA -format that requires translation before it is shown correctly in DataStage. In order to make the translation you must tell the odbc-driver which character set is used in AS400. You tell this by adding following line to .odbc.ini for the connection in question:

Code: Select all

CharsetFor65535=2028 

Here IANACodePage 2028 refers to IBM037 encoding which is the basic US-English. More code pages can be found from DataDirect's help pages.

Hope this helps someone.