locale problem
ran into this thread - which mirrors our situation. can folks comment on the part about "possible data corruption"? we are on version 10 FC9. i had heard there might be new features in version 11 FC6 to help with this issue but am not sure of those details. The situation: There is the thought that in order to hold chinese language characters, we could create a new UTF8 db on our current instance and the web application would hit the new DB and also hit the current en_US.819 db and combine some chinese character with english text - but the mention of possible corruption tells me this is not safe and sound to apply to our cash cow DB. Thanks Tom ----------------------------------------------------------------- POSTING------------------- Hi All, I have one interesting problem in informix. I have two databases in one instance. One database has been created with DB_LOCALE=en_US.819 Another database has been created with DB_LOCALE=en_US.UTF8 We have a query, which is collecting a data from both the databases. It is giving error "locale information mismatch". How can i solve this scenario? If i set DB_LOCALE=en_US.819 then , i couldn't execute the query on other database. If i set DB_LOCALE=en_US.UTF8, first database query is not working. ========================================================================== REPLY-- There is a way (Guy Bowerman's blog site) to solve it but it is not recommended. The article finishes with the sentence... can potentially lead to data corruption. So if you have critical data in your databases is better to perform the safe solution. The two databases have to speak the same language. So the safe solution is to export the one, change DB_LOCALE to be the same with the other and import again. ================================================= post is located here: http://www.dbforums.com/informix/1644107-locale-problem.html and guy bowerman's blog on this is here: https://www.ibm.com/developerworks/mydeveloperworks/blogs/gbowerman/entry/trouble_with_locales
I'm sorry for "top posting", but I need too look at your comments while answering ;) This is a very complex issue and I'm not sure I understand the scenario completely. First, if you continue digging you may find other posts (I've been through this before) about similar situations with more detailed explanations. The question you quote is not exactly what Guy wrote about. And I didn't quite understand if your application queries bothe databases through the same connection. If it does, then I don't think you can solve this without having same database locales on both. Currently you can't make a distributed query on databases with different locales. The variable that is referenced on Guy's blog may not solve that (I would have to check). In any case, if it "solves", you'd just be hidding the problem (and eventually entering a bigger one...). I have no knowledge about any xC6 feature that may help this, although it does ring a bell (maybe just a feature request or something...) Now... to the more practical part... "database locale mismatch" is an error you'll get on V10+ (maybe 10.00.FC3+) when you connect to a database created with local X, while having DB_LOCALE set to Y. This is a fix to an old problem where the connection was allowed but then you'd be entering CLIENT_LOCALE characters into a different locale database. The typical scenario was seen on Windows application that used ODBC. By default the ODBC was setup with CLIENT_LOCALE=en_us.CP1252 and DB_LOCALE=en_us.CP1252. If this ODBC connnected to a Unix database created with the default locale (en_us.819) you would store windows only characters into a character set that don't have them... It would work out for that application... But after fixing the above (imagine you moved into 10.00.FC8), you'd get the error. And the solution would be to set DB_LOCALE=en_us.819. But then you could hit another error, because the client driver would see invalid characters (it expected only 819 codeset and it would receive CP1252)... This can be considered "corruption", although if you use Guy workaround, you could still use DB_LOCALE=en_us.CP1252 and the application would work ok again... This was all based on *one* application accessing *one* database. If your scenario is different (the same connection accesses both) than the situation is more complex... *SPECULATION MODE* I don't know if the variable allows cross database queries to work without error. But assuming it does, you can get in trouble because it will probably tell the engine to ignore LOCALE differences. And it won't do character conversions... so you'll try to use 819 characters in UTF8 context or vice versa... that can probably cause "corruption". But I'd have to check UTF8 again... If I recall correctly it has the same codes as one iso-8859-X for characters that exist in this later...? Anyway, even if your application worked, it would be a hack, not a solution. And sooner or later it would probably catch you again... Tom Lehr wrote: > ran into this thread - which mirrors our situation. > > can folks comment on the part about "possible data corruption"? > > we are on version 10 FC9. > > i had heard there might be new features in version 11 FC6 to help with > this issue but am not sure of those details. > > The situation: > There is the thought that in order to hold chinese language > characters, we could create a new UTF8 db on our current instance and > the web application would hit the new DB and also hit the current > en_US.819 db and combine some chinese character with english text - > but the mention of possible corruption tells me this is not safe and > sound to apply to our cash cow DB. > > Thanks > Tom > > > ----------------------------------------------------------------- > POSTING------------------- > Hi All, > > I have one interesting problem in informix. > I have two databases in one instance. > > One database has been created with DB_LOCALE=en_US.819 > Another database has been created with DB_LOCALE=en_US.UTF8 > > We have a query, which is collecting a data from both the databases. > It is giving error "locale information mismatch". > > How can i solve this scenario? > > If i set DB_LOCALE=en_US.819 then , i couldn't execute the query on > other database. If i set DB_LOCALE=en_US.UTF8, first database query is > not working. > ========================================================================== > REPLY-- > There is a way (Guy Bowerman's blog site) > to solve it but it is not recommended. The article finishes with the > sentence... can potentially lead to data corruption. > > So if you have critical data in your databases is better to perform > the safe solution. > > The two databases have to speak the same language. So the safe > solution is to export the one, change DB_LOCALE to be the same with > the other and import again. > > > ================================================= > > post is located here: > http://www.dbforums.com/informix/1644107-locale-problem.html > > and guy bowerman's blog on this is here: > https://www.ibm.com/developerworks/mydeveloperworks/blogs/gbowerman/entry/trouble_with_locales
Fernando, Thank you much for your reply! Yes, Guy's blog looks related to the 819 versus1252 problem rather than our specific situation. our scenario would be trying to create a new db (that could contain chinese) on a pre-existing instance that is configured for just english (819). (we are on v10.FC9) A web application would have to hit both db's in order to display the chinese character and the english text together. If I am reading your post correctly, ("Currently you can't make a distributed query on databases with different locales.") , then I don't think this approach is going to work. I don't think I can just switch the whole instance and all of its databases to be UTF8 so that means the only choice left is to export and import every db on the instance and have the locale be utf8. as far as a next release, this was sent in an email from ibm support but no more details regarding it: "The next release of IDS, 11.50.xC6 has features that are meant to help with this translation phase." Tom On Nov 12, 6:52 pm, Fernando Nunes <domusonl...@gmail.com> wrote: > I'm sorry for "top posting", but I need too look at your comments while > answering ;) > This is a very complex issue and I'm not sure I understand the scenario > completely. > First, if you continue digging you may find other posts (I've been > through this before) about similar situations with more detailed > explanations. > > The question you quote is not exactly what Guy wrote about. And I didn't > quite understand if your application queries bothe databases through the > same connection. If it does, then I don't think you can solve this > without having same database locales on both. Currently you can't make a > distributed query on databases with different locales. The variable that > is referenced on Guy's blog may not solve that (I would have to check). > In any case, if it "solves", you'd just be hidding the problem (and > eventually entering a bigger one...). > I have no knowledge about any xC6 feature that may help this, although > it does ring a bell (maybe just a feature request or something...) > > Now... to the more practical part... > > "database locale mismatch" is an error you'll get on V10+ (maybe > 10.00.FC3+) when you connect to a database created with local X, while > having DB_LOCALE set to Y. This is a fix to an old problem where the > connection was allowed but then you'd be entering CLIENT_LOCALE > characters into a different locale database. The typical scenario was > seen on Windows application that used ODBC. By default the ODBC was > setup with CLIENT_LOCALE=en_us.CP1252 and DB_LOCALE=en_us.CP1252. If > this ODBC connnected to a Unix database created with the default locale > (en_us.819) you would store windows only characters into a character set > that don't have them... It would work out for that application... But > after fixing the above (imagine you moved into 10.00.FC8), you'd get the > error. And the solution would be to set DB_LOCALE=en_us.819. But then > you could hit another error, because the client driver would see invalid > characters (it expected only 819 codeset and it would receive CP1252)... > > This can be considered "corruption", although if you use Guy workaround, > you could still use DB_LOCALE=en_us.CP1252 and the application would > work ok again... > > This was all based on *one* application accessing *one* database. If > your scenario is different (the same connection accesses both) than the > situation is more complex... > > *SPECULATION MODE* > I don't know if the variable allows cross database queries to work > without error. But assuming it does, you can get in trouble because it > will probably tell the engine to ignore LOCALE differences. And it won't > do character conversions... so you'll try to use 819 characters in UTF8 > context or vice versa... that can probably cause "corruption". But I'd > have to check UTF8 again... If I recall correctly it has the same codes > as one iso-8859-X for characters that exist in this later...? > Anyway, even if your application worked, it would be a hack, not a > solution. And sooner or later it would probably catch you again... > > > > Tom Lehr wrote: > > ran into this thread - which mirrors our situation. > > > can folks comment on the part about "possible data corruption"? > > > we are on version 10 FC9. > > > i had heard there might be new features in version 11 FC6 to help with > > this issue but am not sure of those details. > > > The situation: > > There is the thought that in order to hold chinese language > > characters, we could create a new UTF8 db on our current instance and > > the web application would hit the new DB and also hit the current > > en_US.819 db and combine some chinese character with english text - > > but the mention of possible corruption tells me this is not safe and > > sound to apply to our cash cow DB. > > > Thanks > > Tom > > > ----------------------------------------------------------------- > > POSTING------------------- > > Hi All, > > > I have one interesting problem in informix. > > I have two databases in one instance. > > > One database has been created with DB_LOCALE=en_US.819 > > Another database has been created with DB_LOCALE=en_US.UTF8 > > > We have a query, which is collecting a data from both the databases. > > It is giving error "locale information mismatch". > > > How can i solve this scenario? > > > If i set DB_LOCALE=en_US.819 then , i couldn't execute the query on > > other database. If i set DB_LOCALE=en_US.UTF8, first database query is > > not working. > > ========================================================================== > > REPLY-- > > There is a way (Guy Bowerman's blog site) > > to solve it but it is not recommended. The article finishes with the > > sentence... can potentially lead to data corruption. > > > So if you have critical data in your databases is better to perform > > the safe solution. > > > The two databases have to speak the same language. So the safe > > solution is to export the one, change DB_LOCALE to be the same with > > the other and import again. > > > ================================================= > > > post is located here: > >http://www.dbforums.com/informix/1644107-locale-problem.html > > > and guy bowerman's blog on this is here: > >https://www.ibm.com/developerworks/mydeveloperworks/blogs/gbowerman/e...- Hide quoted text - > > - Show quoted text -