In a multi-lingual application, you can only have one database collation which determines the sorting of indexes. Since there are different sorting rules for different languages, it should be possible to use several collations.
Allow loading several collations in one database so that there could be several instances of each index (one instance for Basic collation, another instance for ICU-FI, and so on). The index instance to be used should depend on the session's -cpcoll. We should be able to select the possible collations for each index individually so that some indexes might only have one instance (for performance reasons) and some would have multiple instances, one for each language.
Also allow the session collation (SESSION:CPCOLL) to be changed programmatically so that we can change it on-the-fly based on user's language. Now we have to use different .pf files for different user languages.
Support for multiple collations in a single database is on our Database roadmap. In fact, in a case of forward-thinking, when we worked on OE 11.0 we took advantage of the opportunity to put in the metaschema changes that would be needed to make multiple collations possible. However, the remaining development work for this capability is quite extensive and with the other Database features that have taken precedence recently, this one has not yet made it to the top of the priority list. But it is indeed "on the list".
Thanks, Rob, for the information!
yes, what is the use of UTF-8 databases when you only have 1 collation..
now you need to create multiple DB to support this...
This is a very good idea. We use multi tenant UTF-8 databases, where each tenant represents a country. So the ability to use a different collation per tenant would be really an improvement.1