We help IT Professionals succeed at work.

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

1,292 Views
Last Modified: 2017-04-06
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

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.
Expert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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?
Gary Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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 Patterson, CISSPExpert for hire: IBM i, AIX, Linux, Windows, DB2, Performance, Security. EDI
CERTIFIED EXPERT

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions