Hi

For an international website, I need to have control over the sorting of characters.

For german users, the sorting a, ä, b .. z may be correct but swedish users like a .. z, ä ..

I use DB2 7.2 with FP13 on Win2k and configured it as an UTF-8 DB.

I have read a lot of forum messages and redbooks, but found no solution.
Thanks in advance for any help.

Hoggar74 (frustrated)

I remembered there is something called field proc, which is associated with column, was designed for this purposed. Sorry, that I never use it.

You may want to look at the discussion of string comparison in the SQL reference. The way I understand it, the DB2 engine will sort strings according to the collating table specified at the time of the database creation. Since you can't change it "on the fly" you may have to sort the results in the application, depending on the user's current locale (or whatever way you have to determine user's preferences).

Hi Hoggar74,

I opened a PMR for db2 7.2 database few months ago and I got answer from IBM: if using "UTF8 character code" then only one sorting is posible: according to collating sequence. I was very surprised, because if "correct" sorting is not posible then UTF8 code page is useless for me.

This is bad for IBM DB2 database system, because I got information from competitive company that Oracle and Informics supports sorting with UTF8 character code system.

I hope IBM will add support for sorting when UTF8 is used.

Hope this helps,
Grofaty

Phu!

Bad news for me. But bad news is even better than no news.
So I will migrate my data and switch to Oracle.

Thanks for your effort!
Hoggar74

hi,

Sorry to hear about it. DB2 definitely supports "sorting with UTF8 character code system." As I knew, basically, all the system support sort by hex value. That is, if your db is in UTF8, the sort is performanced by UTF8's hex value. If your db is ascii or ebcdic, the sort is by its ascii or ebcdic's hex value.

I think the customer service's answer is "db2 only support one sorting, that is sorted by hex value".

For example, from sql lang point of view, the following two stmts are basically the same if the stmt reach the data level.

SELECT * FROM TABLE ORDER BY C1;
SELECT * FROM TABLE ORDER BY HEX(C1)

the second stmt called sort by expression, is only supported by the recent release.

I am not quite understand the original post, it seems that the application wan t to have different result on : SELECT * FROM TABLE ORDER BY C1, according to where the clients are?
It is really difficult. I don't think oracle can do it without applicaiton side's help.

For the record, the way that I would do this would be to create a UDF that takes the source string and the desired sort language (eg Swedish, German etc) and produces a hex byte string that contained the desired sort sequence. Eg
A Swedish - x'0100'; German - x'0100'
a - x'0101'; - x'0100'
Z - x'1a00'; - x'1a00'
z - x'1a01'; - x'1a01'
Ä - x'1b00'; - x'0102'
ä - x'1b01'; - x'0103'

and then order on the resulting hex string.

This would also do such things as produce a suitable sort position for, eg, Ç in the various languages.

Hi nidm,

I was probably not understood correctly. I opened PMR for db2 7.2 on Win/2K few months ago. IBM answered to me: "This is not a bug. Sorting is only posible by hex value". But sorting to hex value is useless to me... So they suggested me to open MFR (marketing field request). MFR is opened if some customer would like to have any new feature that existing version does not supports. So I opened MFR and now I am just waitting to get some answer.

Thanks,
Grofaty

So be patient. I opened one about 3 years ago and received no feedback since then.

Hi, Grofaty.

Thanks for the input.

About MFR, it is "true" as the above post suggests. Even though DB2 would implement as you suggest, it will take at least 3 years(two years to implement a major release and one year ahead to design what features will be inlcuded inside the release). And most time, only when the MFR represents many customers or a big customer like siebel/sap/american express...., it will be considered seriously. :-) Well, I agree that cusotmer service personal should at least give you an update.

FYI, I heard that IBM is planning such kind of sort option in V9. If you have channel to access IBM's development plan, you may be able to verify it. For today, a UDF mentioned in previous post will help though the performance won't be great.

nidm

Yes, that's the main problem. I can understand why some single wish won't be implemented but it should be standard to give some feedback. I never heard anything about it, the MFR process just looks like a black hole.