Using IBMDA400 OLE DB driver to create linked server in MSSQL, need help translating data from CCSID 65535

Bill Johnstone
Bill Johnstone used Ask the Experts™
on
I have installed Client Access (7.1) on our MS SQL 2014 server.  I am using the IBMDA400 OLE driver to create a linked server to DB2 database one the iSeries. When I run a select statement on the data in a table a couple of fields are legible but most did not translate.

The fields that translate properly are ccsid 37 the ones that did not are ccsid 65535.

I have read several posts and tried putting various translation options in the Provider string.  It seems as soon as I add add any additional string other than the "Default Collection = xxx" in the provider string the connection test fails.

Can anyone steer me in the right direction?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Technically, if data is tagged as CCSID 65535, there is no conversion -- it's not text data, but rather binary data such as an image.

However, because many older systems were put in service with CCSID 65535 as the default, i.e., they were left in their "from the factory" state, there are many cases where text is stored as CCSID 65535 decades after a meaningful CCSID attribute should have been assigned. So, it's likely that a connection attribute to cause conversion as if the text data was properly defined in the database would be useful. (Best would be to correct the database column definition and fix any apps that use it.)

In any case, we need to see actual code for both what works and what fails. Any info on exactly what client version/fix level should also be supplied. There may be other issues as well, such as correcting user profile attributes on the server and language settings on the client, but details of the actual problem give a good starting point.
VP Technology / Senior Consultant
Commented:
Show your code/connection string  please!

You probably just have a syntax error in the connection string.  Don't put extra spaces in the provider string.  Example string, properly formatted:

Force Translate=1;Catalog Library List=AMFLIB,QGPL,PRODLIB;Block Fetch=true;

Good TechNotes for this task:

Configuring an IBM i DB2 (iSeries / AS/400) OLE DB Provider on an SQL Server:
http://www-01.ibm.com/support/docview.wss?uid=nas8N1014514

iSeries Access for Windows Custom Connection Properties for OLE DB
http://www-01.ibm.com/support/docview.wss?uid=nas8N1017400

Make particular note of "Force Translate" property.  It will solve your CCSID 66535 issues.  Another alternative is to fix the CCSID on those columns in the database ...  If they hold CCSID 37 data, then they should really be flagged CCSID 37.

Author

Commented:
Hi Gary

You were right, it was the syntax.  I had been putting in spaces. Once I removed them the connection creates successfully and the data is translating correctly.

Now that it seems to work, I have tested some variations of the provider string statement.
 
All of these translate correctly.

"Force Translate=0;Default Collection=cltdta"
"Force Translate=1;Default Collection=cltdta"
"Force Translate=37;Default Collection=cltdta"

All of these do not translate.

"Force Translate=65535;Default Collection=cltdta"
"Default Collection=cltdta"

Can you tell me which is most correct to use and possibly why the others "are working" or "appear to work" as well?

And a second question, I am using the IBMDA400 OLE DB driver, there is also an IBMDASQL OLE DB driver, is there a reason I should use one over the other?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Gary PattersonVP Technology / Senior Consultant
Commented:
Generally IBM i data is encoded in EBCDIC.  There are a number of different EBCDIC CCSIDs, for different countries.  CCSID 37 is US EBCDIC.  

The OLE DB provider knows that PC programs don't deal well with EBCDIC, so it provides automatic conversion to the appropriate Windows code page by default.  But to do this, it needs to know the CCSID that each columns is encoded with.  CCSID 65535 columns can be a problem, if they really contains data encoded in CCSID 37.  65535 means "binary data - do not convert".  As Tom said above, this is a common problem.

Assuming the CCSID 65535 data is actually encoded in CCSID 37, use Force Translate=37: which means to assume that CCSID 65535 columns are really encoded in CCSID 37.

If the database server jobs start up using a default CCSID of 37 (they usually do on a US-localized system), you can also use Force Tran late=0, which just says to translate 65535 as if is is the default CCSID used by the database server job running the request.

Force Translate=65535 is meaningless.  Translate 65535 as if it was 65535.  65535 means "binary data - do not translate".
Force Translate=1 looks meaningless to me.  "Translate 65535 data as if it was CCSID 1".  There is no CCSID 1, as far as I know.  Not sure what this would do.

Review the Force Translate explanation in the second link above - it covers this all in detail.

Also, if you'll review the N1014514 technote link above, it explains the difference between the different providers.  You'll want to use IBMDASQL for a linked server from SQL Server. since it supports MTS and commitment control.

Author

Commented:
Thanks for the clarification.  I appreciate all the help.

Author

Commented:
Thanks again
If the database server jobs start up using a default CCSID of 37 (they usually do on a US-localized system), you can also use Force Tran late=0, which just says to translate 65535 as it is is the default CCSID used by the database server job running the request.
This is only an assumed minor (possible) typo correction. It looks to me as if the intended wording is:
If the database server jobs start up using a default CCSID of 37 (they usually do on a US-localized system), you can also use Force Tran late=0, which just says to translate 65535 as if it is in the default CCSID used by the database server job running the request.
"Force Translate" seemed like the the correct connection property name, but I've seen at least two additional ones depending (apparently) on the specific product version and release.

For "Force Translate=1", I was pretty sure that it was the opposite of "Force Translate=0". There is no defined 'CCSID 1' (00037 is the lowest CCSID value defined in IBM i systems), so it would be a "special" value. And because "0" should cause a translation, "1" would disable translation. But if it results in correct character values on the client system, it makes less sense to me.
Gary PattersonVP Technology / Senior Consultant

Commented:
Toms correction is right.  I will edit my response.  Thanks Tom.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial