JOIN databases of different charsets

#1

Hi,

We several databases in informix, some are utf-8, others are iso-8859-1.

Is it possible to do a JOIN query from the two databases without getting a "database locale mismatch"?

I think the problem is ifx needs to compare data in different charsets internally for the JOIN, which is not possible. But maybe there's a way to force internal charset conversion for the JOIN even if it would result in slow queries.

We're planning on migrating more databases from ISO-8859 to UTF-8 and it would help immensely to be able to JOIN two databases of different charsets during the migration period.

Running Informix 11.70.UC1.

Regards,

Aleksander Kamenik
System Administrator
Krediidiinfo AS
an Experian Company
Phone: +372 665 9649
Email: aleksander (AT) krediidiinfo (DOT) ee

#2

Quote:

From: Fernando Nunes [mailto:domusonline (AT) gmail (DOT) com]

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.

You are correct.

Quote:

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/m.../gbowerman/ent
ry/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...

I'll check this out on a test instance. See what the queries with JOINS will return, I doubt the results will be good. As long as we're doing SELECT/read-only then there should not be any corruption though? Then again the idea of a misconfigured client screwing up the tables with incorrect locales sounds bad enough.

The correct solution is probably to push the migrations and get this over with as fast as possible. The sooner we're in utf only land, the better.

Regards,

Aleksander Kamenik
System Administrator
Krediidiinfo AS
an Experian Company
Phone: +372 665 9649
Email: aleksander (AT) krediidiinfo (DOT) ee

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

JOIN databases of different charsets

comp.databases.informix comp.databases.informix

JOIN databases of different charsets - 03-23-2011 , 07:51 AM

RE: JOIN databases of different charsets - 03-24-2011 , 03:10 AM