Locale Settings for ODBC

All, Last weekend we upgraded IDS for one of our applications from 9.40.FC6 to 9.40.FC7W4. We are running 64-bit IDS on 64-bit HP-UX 11.11. I performed the upgrade by shutting down all the instances on each box, changing the link that points to the binaries from the old to the new, and then starting up each instance. All went well and the application still works fine. The problem we now have has to do with users who use Crystal Reports via ODBC to access data in these databases. The users are getting an connect error that says: "Test connection was NOT successful.[Informix][Informix ODBC Driver][Informix]Database locale information mismatch." If they go into their configuration settings on their PC and enter en_us.8859-1 in for the Database Locale, it works. This field is currently blank on their PC's. Unfortunately, there are many of these users and they are running Crystal off many different PC's. According to the users, they're not even sure who is running all these reports. Additionally, they feel they should not have to make changes on their end if we make a change on our end. Can't say I blame them. It is almost as if the engine used to be forgiving and used the default (en_us.8859-1) if it was not supplied by the user but now it is no longer forgiving and requires the db lcale to be sent. Is there a setting at the IDS server level we can change to eliminate this error for the users? We are probably going to have to open a case with IBM but I thought I would see if anyone out there has run across this in the past. Thanks in advance. Rob Schmitz Informix DBA rob.b.schmitz@sprint.com

You need to have this exported in your environment, and then start the engine, to allow the permissiveness once again. export IFMX_UNDOC_B168163=1 Regards, Joe Plugge Informix DBA West Corporation ________________________________ From: ids-bounces@iiug.org on behalf of Schmitz, Ro.... Sent: Tue 3/14/2006 4:18 PM To: ids@iiug.org Subject: Locale Settings for ODBC [6507] All, Last weekend we upgraded IDS for one of our applications from 9.40.FC6 to 9.40.FC7W4. We are running 64-bit IDS on 64-bit HP-UX 11.11. I performed the upgrade by shutting down all the instances on each box, changing the link that points to the binaries from the old to the new, and then starting up each instance. All went well and the application still works fine. The problem we now have has to do with users who use Crystal Reports via ODBC to access data in these databases. The users are getting an connect error that says: "Test connection was NOT successful.[Informix][Informix ODBC Driver][Informix]Database locale information mismatch." If they go into their configuration settings on their PC and enter en_us.8859-1 in for the Database Locale, it works. This field is currently blank on their PC's. Unfortunately, there are many of these users and they are running Crystal off many different PC's. According to the users, they're not even sure who is running all these reports. Additionally, they feel they should not have to make changes on their end if we make a change on our end. Can't say I blame them. It is almost as if the engine used to be forgiving and used the default (en_us.8859-1) if it was not supplied by the user but now it is no longer forgiving and requires the db lcale to be sent. Is there a setting at the IDS server level we can change to eliminate this error for the users? We are probably going to have to open a case with IBM but I thought I would see if anyone out there has run across this in the past. Thanks in advance. Rob Schmitz Informix DBA rob.b.schmitz@sprint.com ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Plugge, Joe R. said: > > You need to have this exported in your environment, and then start the > engine, > to allow the permissiveness once again. > > export IFMX_UNDOC_B168163=1 This may well work, and I hope it does. But can I just urge a degree of caution about using undocumented features without at least chatting with Tech Support? -- Bye now, Obnoxio "It's easier with pictures." -- Cosmo "But wait, it gets worse." -- Cosmo "Run, don't walk, for the nearest exit." -- Cosmo

I feel your pain. I upgraded 10.00.UC3 to 10.00.UC4 and whacked our ODBC connections. "We're getting 'locale' errors. Why did you change the 'locale' setting??" "I didn't change anything!" Luckily, once we figured it out we only had to redo a few DSNs on a few SQL Server boxes used for BI reporting programs. Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Schmitz, Ro.... Sent: Tuesday, March 14, 2006 5:18 PM To: ids@iiug.org Subject: Locale Settings for ODBC [6507] All, Last weekend we upgraded IDS for one of our applications from 9.40.FC6 to 9.40.FC7W4. We are running 64-bit IDS on 64-bit HP-UX 11.11. I performed the upgrade by shutting down all the instances on each box, changing the link that points to the binaries from the old to the new, and then starting up each instance. All went well and the application still works fine. The problem we now have has to do with users who use Crystal Reports via ODBC to access data in these databases. The users are getting an connect error that says: "Test connection was NOT successful.[Informix][Informix ODBC Driver][Informix]Database locale information mismatch." If they go into their configuration settings on their PC and enter en_us.8859-1 in for the Database Locale, it works. This field is currently blank on their PC's. Unfortunately, there are many of these users and they are running Crystal off many different PC's. According to the users, they're not even sure who is running all these reports. Additionally, they feel they should not have to make changes on their end if we make a change on our end. Can't say I blame them. It is almost as if the engine used to be forgiving and used the default (en_us.8859-1) if it was not supplied by the user but now it is no longer forgiving and requires the db lcale to be sent. Is there a setting at the IDS server level we can change to eliminate this error for the users? We are probably going to have to open a case with IBM but I thought I would see if anyone out there has run across this in the past. Thanks in advance. Rob Schmitz Informix DBA rob.b.schmitz@sprint.com ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

That is where I got the advice ;-) -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Obnoxio The.... Sent: Wednesday, March 15, 2006 3:56 AM To: ids@iiug.org Subject: RE: Locale Settings for ODBC [6510] Plugge, Joe R. said: > > You need to have this exported in your environment, and then start the > engine, to allow the permissiveness once again. > > export IFMX_UNDOC_B168163=1 This may well work, and I hope it does. But can I just urge a degree of caution about using undocumented features without at least chatting with Tech Support? -- Bye now, Obnoxio "It's easier with pictures." -- Cosmo "But wait, it gets worse." -- Cosmo "Run, don't walk, for the nearest exit." -- Cosmo ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Plugge, Joe R. said: > > That is where I got the advice ;-) I'm sure you did -- but it might not be relevant to this case, or there might be unintended consequences of using this. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Obnoxio The.... > Sent: Wednesday, March 15, 2006 3:56 AM > To: ids@iiug.org > Subject: RE: Locale Settings for ODBC [6510] > > Plugge, Joe R. said: >> >> You need to have this exported in your environment, and then start the > >> engine, to allow the permissiveness once again. >> >> export IFMX_UNDOC_B168163=1 > > This may well work, and I hope it does. But can I just urge a degree of > caution about using undocumented features without at least chatting with > Tech Support? > > -- > Bye now, > Obnoxio > > "It's easier with pictures." > -- Cosmo > > "But wait, it gets worse." > -- Cosmo > > "Run, don't walk, for the nearest exit." > -- Cosmo > > ************************************************************************ > ******* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Bye now, Obnoxio "It's easier with pictures." -- Cosmo "But wait, it gets worse." -- Cosmo "Run, don't walk, for the nearest exit." -- Cosmo

This hit us when we upgraded from FC3 to FC3X2 and FC4. In order to resolve this issue for all of our apps I had to modify the DB_LOCALE settings in 3 different places. 1) For existing ODBC connections I had to change the Database Locale to match the database servers locale. 2) For All new ODBC connections and for .NET connectivity, which at this point (CDSK 2.81.TCx, 2.90.TC4) is built on top of the ODBC layer, I had to add an environment variable DB_LOCALE=en_US.819. 3) For DB2 II (Information Integrator) connections I had to change the DB_LOCALE value in setnet32 DL Redden Lisha Lynn Mineral Cosmetics ----- Original Message ---- From: "Schmitz, Ro...." <Rob.B.Schmitz@sprint.com> To: ids@iiug.org Sent: Tuesday, March 14, 2006 4:18:07 PM Subject: Locale Settings for ODBC [6507] All, Last weekend we upgraded IDS for one of our applications from 9.40.FC6 to 9.40.FC7W4. We are running 64-bit IDS on 64-bit HP-UX 11.11. I performed the upgrade by shutting down all the instances on each box, changing the link that points to the binaries from the old to the new, and then starting up each instance. All went well and the application still works fine. The problem we now have has to do with users who use Crystal Reports via ODBC to access data in these databases. The users are getting an connect error that says: "Test connection was NOT successful.[Informix][Informix ODBC Driver][Informix]Database locale information mismatch." If they go into their configuration settings on their PC and enter en_us.8859-1 in for the Database Locale, it works. This field is currently blank on their PC's. Unfortunately, there are many of these users and they are running Crystal off many different PC's. According to the users, they're not even sure who is running all these reports. Additionally, they feel they should not have to make changes on their end if we make a change on our end. Can't say I blame them. It is almost as if the engine used to be forgiving and used the default (en_us.8859-1) if it was not supplied by the user but now it is no longer forgiving and requires the db lcale to be sent. Is there a setting at the IDS server level we can change to eliminate this error for the users? We are probably going to have to open a case with IBM but I thought I would see if anyone out there has run across this in the past. Thanks in advance. Rob Schmitz Informix DBA rob.b.schmitz@sprint.com ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

>This hit us when we upgraded from FC3 to FC3X2 and FC4. In order to resolve this issue for all of our apps I had to modify the DB_LOCALE settings in 3 different places. > >1) For existing ODBC connections I had to change the Database Locale to match the database servers locale. >2) For All new ODBC connections and for .NET connectivity, which at this point (CDSK 2.81.TCx, 2.90.TC4) is built on top of the ODBC layer, I had to add an environment >variable DB_LOCALE=en_US.819. >3) For DB2 II (Information Integrator) connections I had to change the DB_LOCALE value in setnet32 >DL Redden I believe you can set IFMX_UNDOC_B168163=1 when you start your engine and have the old behavior That appears to ignore the Locale stuff.

I believe you can set IFMX_UNDOC_B168163=1 when you start your engine and have the old behavior That appears to ignore the Locale stuff. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. Sorry this was already covered. And OTC, ALL undocumented crap is use at your own risk :)

Thanks to everyone who responded regarding this issue. I checked with Tech Support and indeed setting the environment variable IFMX_UNDOC_B168163 to 1 and bouncing the engine did the trick. Here is a brief explanation of why: There had been a bug regarding the matching process for client code sets vs. the database code set. To fix that, they tightened the process - but a little too tight. In my case, the Windows default code set is en_us.1252 (client) and the UNIX default is en_us.8859-1 (database). These are nearly identical code sets but the "tight" process disallowed a connection. Setting the environment variable "loosens up" the code set matching process a bit and allows code sets that are fairly close to each other to count as a match. Very different code sets will still cause an unsuccessful connection. Just a little bit of information to tuck away somewhere. As suggested, it is always a good idea to run something like this by Tech Support - even though I value the knowledge in the IIUG! Rob Schmitz Informix DBA rob.b.schmitz@sprint.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Obnoxio The.... Sent: Wednesday, March 15, 2006 3:56 AM To: ids@iiug.org Subject: RE: Locale Settings for ODBC [6510] Plugge, Joe R. said: > > You need to have this exported in your environment, and then start the > engine, > to allow the permissiveness once again. > > export IFMX_UNDOC_B168163=1 This may well work, and I hope it does. But can I just urge a degree of caution about using undocumented features without at least chatting with Tech Support? -- Bye now, Obnoxio "It's easier with pictures." -- Cosmo "But wait, it gets worse." -- Cosmo "Run, don't walk, for the nearest exit." -- Cosmo ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.