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

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?
Bill JohnstoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Gary PattersonVP 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:

iSeries Access for Windows Custom Connection Properties for OLE DB

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill JohnstoneAuthor 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 Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

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.
Bill JohnstoneAuthor Commented:
Thanks for the clarification.  I appreciate all the help.
Bill JohnstoneAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.