• Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

Comments (29)

1 localhost commented Permalink

Thanks for the comment and for describing how easily a problem can occur when data is inserted with mismatched locales. You're right about 2.90.TC5 - I've amended my post to say the locale setting is fixed in 2.90.TC6 and higher (available as a patch from tech support for people who need it earlier).

I understand the frustration that this wasn't caught earlier. The mismatch couldn't be allowed to continue, there are potentially worse problems going from EN_US.CP1252 to EN_US.UTF8. Fixing it has become a Hobson's choice - there's no alternative.
Mark, get in touch with me if you'd like to discuss this further. I've been chatting to one of the engineers who worked on these bug fixes and we can set up a conference call with him to discuss the problem in more depth if you're interested.

2 localhost commented Permalink

Mmmm, this is the biggest can of worms IBM has opened (closed?) in a long time.

What happens to customers who didn't notice that the default locale settings were mismatched, and had users entering character data that included extended characters (because they had cut&paste it from Word documents) for example? Now Word is very nice to you and does things like convert 3 dots into a single character (ASCII 133) automagically. Or what about users who entered the Euro symbol (ASCII 128) into character fields in all innocence?
So we come to upgrade IDS to 9.40.FC8 (or FC7, yes this problem exists for 9.4 as well) and find that queries are now breaking with error -21005 when these characters are referenced. You can STILL insert these extended characters, unless you install CSDK 2.90.TC5, which now gives the same error on INSERT. What CSDK 2.90.TC5 DOESN'T do is set the default locale correctly, it is still set to en_US.CP1252.
But all this seems to be bolting the stable door after the horse has run away, won the Grand National, and been put out to pasture. What about all this data in the database that we now can't get at? We have two options according to IBM support:
1. Change the locale of the database, which involves dbexport/dbimport.... (on a 24/7 250Gb database!! I think not!)2. Use the strangely named environment variable mentioned above (and remember to set it before every bounce).
Nice one! Thanks IBM!

3 localhost commented Permalink

Thank you for revealing this issue, it describes our current situation exactly.

I am not a DBA, rather I am third tier support for a telecommunications application that includes an Informix database. Given that disclaimer here are my questions:
1. My customer has stated he cannot change the client side at this time and he does not use a DSN. Therefore I need to make the change on the server side. Is this the situation where I add the variable IFMX_UNDOC_B168163 with a value of 1 to the server?
2. If this variable is added to the server where is it added?
3. You mentioned potential data corruption. How likely is this to occur? Our application uses a medium (at most) size database with a large number of transactions. It is not a complicated or intense database application
4. Given that I cannot touch the client side, what is the approach you recommend for resolving this issue?
Thank you very much for your help with this.

4 localhost commented Permalink

An additional question.....

5. Is there a way to specify the client locale without the use of a DSN?
Thanks again.

5 localhost commented Permalink

I'll try to answer the questions...

1) Yes, this situation is where the customer would use the IFMX_UNDOC_B168163 variable. I would discourage the use of this variable.
2) IFMX_UNDOC_B168163 is an environment variable, so you'll need to set it in your environment prior to executing the oninit command to start the server. If the server is on Windows, you can also set the variable in the registry under: HKEY_LOCAL_MACHINE\Software\Informix\Online\%INFORMIXSERVER%\Environment
3) The degree of risk is hard to determine. Corruption occurs when a character is valid in the DB_LOCALE specified on the client connection but is not valid in the DB_LOCALE used when creating the database. For example, the Euro symbol is present in the Windows locale (en_us.cp1252) but is not present in the default Informix locale (en_us.819). So, the degree of risk is determined by which locales are used and what characters may be stored.
4) The ONLY way to avoid corruption is to properly configure the client connections. The code set of the DB_LOCALE specified by the client must match the code set of the DB_LOCALE used in creating the database (a locale name, such as en_us.CP1252, specifies .).
5) Yes, but how you do it depends on what client programming language you're using.

6 localhost commented Permalink


I'm getting the above error message, however my fixes are unsuccessful.
In systables, the rows GL_COLLATE and GL_CTYPES contain a field with a value en_US.819, which according to the docs is a shorthand for en_us.8859-1. This is the default locale, so I should be fine with not specifying a client locale, right? BTW, the server is on an old HP-UX machine.
Anyway, my client is a Java program running on Linux. It throws a SQLException with the above error message. I've tried putting export DB_LOCALE=en_us.8859-1 export CLIENT_LOCALE=en_us.8859-1right before the java command, without success. I've also tried putting these values into the JDBC url, no success either.
Thanks for your help,Viktor

7 localhost commented Permalink

Hi Viktor,

> This is the default locale, so I should be fine with not > specifying a client locale, right?
That depends on the version of the Client SDK you are using. If it's below 2.90.TC6 then the default DB_LOCALE of the client does not match the default DB_LOCALE of the server, and from IDS 10.00.xC4 that generates the mismatch error.
> I've tried putting> export DB_LOCALE=en_us.8859-1> export CLIENT_LOCALE=en_us.8859-1> right before the java command, without success. I've also > tried putting these values into the JDBC url, no success > either.
Setting DB_LOCALE to match the server DB_LOCALE in the JDBC URL should work. Do you get the same database locale mismatch error? Can you run onstat -g env and paste all the locale specific output, and quote the exact JDBC URL you're using? email me directly if you prefer and I can post a summary here. Addr is available at: http://www.ibm.com/contact/employees/us/

8 localhost commented Permalink

Hi,I have the following problem when connecting from the server machine. From any client it is working ...

Thanks fpor any ideas + help
java.sql.SQLException: Server does not support GLS variables DB_LOCALE, CLIENT_LOCALE or GL_DATE
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) at com.informix.jdbc.IfxSqliConnect.a(IfxSqliConnect.java:2634) at com.informix.jdbc.IfxSqli.a(IfxSqli.java:2615) at com.informix.jdbc.IfxSqli.executeOpenDatabase(IfxSqli.java:1800) at com.informix.jdbc.IfxSqliConnect.(IfxSqliConnect.java:1327) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

9 localhost commented Trackback

Hi Dirk,Hard to diagnose that without more information, such as:What is the JDBC URL string you're constructing?What is the exact IDS version?What is the JDBC driver version?If you have a support contract IBM Tech Support is the best place to start with a problem like this. If not, comp.databases.informix newsgroup is next best.Cheers,Guy

10 localhost commented Permalink

Please answer the original question2. If this variable is added to the server where is it added?

I've tried chaning it in .informix and exporting it, all to no avail. do I need to modify the onconfig?

11 localhost commented Trackback

Anthony,re: Please answer the original question 2This question was already answered in Reece Laurie's comment of July 5th. If you're trying to set an IDS server environment variable, on UNIX set it in the informix user's environment before running oninit. On Windows set it in the registry:HKEY_LOCAL_MACHINE/Software/Informix/Online/%INFORMIXSERVER%/EnvironmentRegardsGuy

12 localhost commented Permalink

Hi,I am facing the same problem but my ODBC connections are working without any problem. Whenever I am trying to connect with OLEDB provider, sometime( but frequently) it gives this error message. I am using CSDK Version 2.90.TC2.


13 localhost commented Trackback

Hi Dilip,I don't know the OLEDB provider too well, but as far as I know it gets its Informix environment variables from the registry based on the values set in setnet32. Can you run setnet32 and verify DB_LOCALE matches the value for the server? If your oledb app runs as a different user you might need to run regcopy.exe to make sure the setnet values are set up for the default user.


14 localhost commented Permalink

Dear Guy,Thanks for your comments. I have checked the Environment variables DB_LOCALE and CLIENT_LOCALE in the client PC. Both are set to "en.US819". Then I tried to connect the system databases like sysmaster, syspgm4gl etc through OLEDB provider, which connecting without any problem. I think problem is with databases created by me. How to solve this problem? How to change the locale of my databases?


15 localhost commented Permalink

Setting the value in the UNIX Informix environment is a difficult option for those of us that automatically bring up the servers with an file entry in the /etc/rc2.d directory.

I found out by trial and error that if you make a line entry of "IFMX_UNDOC_B168163 1" in the $INFORMIXDIR/etc/informix.rc file, all instances on the UNIX server will accept the override when brought online.

Add a Comment Add a Comment