Re: JOIN databases of different charsets

I believe Ian is missing the point. The issue is not unloading data and load it into a different locale. Apparently the issue is that the applications require distributed queries between two (or more) databases with different locales. I believe this could be achieved with an undocumented variable setup in the environment. Check this article for further information: https://www.ibm.com/developerworks/mydeveloperworks/blogs/gbowerman/entry/trouble_with_locales?lang=en Now... The idea behind this variable was not allowing this. But the side effect is that it will... The question is: Will this be acceptable? I believe you may hit several problems. You may want to talk to support about this, but I suppose the answer will be that it's unsupported and can potentially lead to corruption or bad query results... Meanwhile, on version 11.x AUS (Auto Update Statistics) also had a similar problem. The task runs in the sysadmin database but it needs to access the catalog information of the target databases. And some customers which had different databases (in the same instance) with different locales hit the problem. A solution was implemented that allows a session to ignore the locale mismatch. It's not documented. Again, tech support or someone else may want to go deeper on this... The side effects should be similar to the above variable... By the way, as a side note, the fact that AUS cannot run in non-logged databases derives from a similar problem: You can't do distributed queries between logged databases (sysadmin) and nonlogged databases (the targets). And this was not solved. So in short, it would be possible, but I think no one will accept the risks (since they're hard/impossible to predict) Regards. On Wed, Mar 23, 2011 at 10:21 PM, Ian Michael Gumby <im_gumby@hotmail.com>wrote: > Aleksander, > > When you unload the data, regardless of the character sets, your data is > going to be a CSV file. That is, the character set strings are going to be > in either a comma or pipe delimited format. > > If you want to do this in Java, you open the file and read each line in, > specifying the character set. > Now I'm going from memory, but I believe the jdbc driver picks up the > character set used when connecting to a specific database? > > So you end up doing the following... > Reading from file, get a row/record. > For each row: > Parse each field and then convert the field from character set A to > UTF-8. > Insert record (Its the exact same schema, right?) as UTF-8. > > So your only 'tricky' part is the character set conversion, which isn't too > difficult. > Actually I believe it is trivial in Java. > > See Java SE 1.6 String constructor : > "*String<http://download.oracle.com/javase/6/docs/api/java/lang/String.html#String%28byte[],%20java.nio.charset.Charset%29>; > *(byte[] bytes, Charset<http://download.oracle.com/javase/6/docs/api/java/nio/charset/Charset.html>; > charset) > Constructs a new String by decoding the specified array of bytes > using the specified charset<http://download.oracle.com/javase/6/docs/api/java/nio/charset/Charset.html>; > ." > > The internal representation is UTF-16 which will get translated > automatically to the correct format within the JDBC insert command. > (Going from memory, which isn't always a good thing.) > > So you can write a simple JDBC java app that selects * from Table A, and > then converts it and then writes out to Database.B.Table A. > > It may be easier than that... When you use Java's JDBC driver, what do you > get when you select from a database? Is it already converted to UTF-16? If > so, you don't need the special constructor. > > > HTH > > -Mike > > > > > > > Subject: RE: JOIN databases of different charsets > > Date: Wed, 23 Mar 2011 21:29:12 +0000 > > From: aleksander.kamenik@krediidiinfo.ee > > To: informix-list@iiug.org > > > > > > From: Ian Michael Gumby [mailto:im_gumby@hotmail.com] > > > > > It looks like you already know the answer to your question. > > > Different character sets will order/compare differently. Its not like > > > you're writing your own comparable operator which would allow you to > > > handle different character sets. > > > > Well theoretically it would be possible for informix first convert the > ISO results to UTF and then do the JOIN. It's not a very popular feature > request I guess. > > > > > At first I thought it would be a cool feature if you could specify the > > > character sets on a per table basis, but then if you think about it... > > > just go UTF-8 which should handle most if not all of your character set > > > needs. (I haven't played with Kanji or other character sets.) > > > > That's why we're migrating to UTF. But it's not as simple as just a > unload/load. > > > > > Since you're going to be converting from one character set to > > > another... you could do it as you unload the table, or as you load the > > > new database table. > > > > The real issue is that we are slowly migrating from one big iso database > to several smaller ones in UTF. It's not just a charset change per table. > New projects use new UTF databases, but they also need data from the old iso > database while not everything has been migrated/updated. New tables with new > schemas are created usually. > > > > It'll probably take more than a year for everything to migrate to UTF > databases. Old projects are rewritten for UTF too, which don't need a schema > changes, but it will still take time. Meanwhile not being able to make JOINs > between new and old databases sucks a bit. > > > > > > Thanks for confirming, > > > > Aleksander Kamenik > > System Administrator > > Krediidiinfo AS > > an Experian Company > > Phone: +372 665 9649 > > Email: aleksander@krediidiinfo.ee > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org > > http://www.iiug.org/mailman/listinfo/informix-list > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...