Heterogeneous Services ODBC based data source and CCSID 13488

Could I have a more complicated scenario.

I’m moving over 4TB of data from an AS/400 to oracle on linux.  We’ve decided to us the linux based ODBC driver and oracle ODBC based gateway & listener combo to get the data from AS/400 to Oracle.  The AS/400 based data is unicode, using CCSID 13488 and GRAPHIC type fields.

This is all good, and is how a JDE database should look when using UNICODE for AS/400.

Problem was, that out of the box, I was getting spaces (or data being read essentially as bytes not a string).

Ouch, this is not how things worked last time.  Last time all of the data was cool.  I tested to see if this was a problem when using generic iSeries windows based ODBC driver, and it was not:

We downloaded and applied the latest V7R1 linux based ODBC drivers and still had the same issue…  Crykie, things are not looking good for my 4TB.

GRAPHIC

This property affects the handling of the graphic (DBCS) data types of GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC, and DBCLOB that have a CCSID other than Unicode (13488). This property affects two different behaviors: 1. Whether graphic fields have their lengths reported as a character count or byte count through the SQLDescribeCol API and SQLColAttribute API with the SQL_COLUMN_LENGTH option. 2. Whether graphic fields are reported as a supported type in the SQLGetTypeInfo result set

0 = Report character count, report as not supported 1 = Report character count, report as supported 2 = Report byte count, report as not supported 3 = Report byte count, report as supported

You can see that from above, that if the ODBC driver is compliant with ODBC standards 3 and above, it should be okay (use NLS_CHAR settings) from the client…  Oh man…

Then, my DBA guru fixed the issue (with help from this slow witted CNC consultant).

Made following changes to initELDORA.ora

HS_NLS_NCHAR=UCS2 ----- this is for graphic datatype

HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252 ------ this is for our database

Made following change to odbc.ini

ForceTranslation = 1

Not too sure of the second option is needed, as this is only supposed to be for 65531 (or what ever it is, not for 13488).

Anyway, after the changes above, we had liftoff:

This made me very happy and allowed me to create some crazy scripts to move all this data from the 400 to oracle.