Avatar of Bill Johnstone
Bill Johnstone
 asked on

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?
DB2IBM System iMicrosoft SQL Server* OLE DB

Avatar of undefined
Last Comment
Gary Patterson, CISSP

8/22/2022 - Mon
Member_2_276102

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.
ASKER CERTIFIED SOLUTION
Gary Patterson, CISSP

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Bill Johnstone

ASKER
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?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Bill Johnstone

ASKER
Thanks for the clarification.  I appreciate all the help.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Bill Johnstone

ASKER
Thanks again
Member_2_276102

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 Patterson, CISSP

Toms correction is right.  I will edit my response.  Thanks Tom.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.