Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    6

    Question Unanswered: international sorting problem

    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)
    Last edited by Hoggar74; 01-13-05 at 05:07.

  2. #2
    Join Date
    May 2003
    Posts
    113
    I remembered there is something called field proc, which is associated with column, was designed for this purposed. Sorry, that I never use it.
    Quote Originally Posted by Hoggar74
    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)

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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).

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    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

  5. #5
    Join Date
    Jan 2005
    Posts
    6

    Angry Bad News

    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

  6. #6
    Join Date
    May 2003
    Posts
    113
    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.


    Quote Originally Posted by grofaty
    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

  7. #7
    Join Date
    Jan 2005
    Posts
    191
    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.

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    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

  9. #9
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Quote Originally Posted by grofaty
    So I opened MFR and now I am just waitting to get some answer.
    So be patient. I opened one about 3 years ago and received no feedback since then.

  10. #10
    Join Date
    May 2003
    Posts
    113
    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
    Quote Originally Posted by grofaty
    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

  11. #11
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96
    Quote Originally Posted by nidm
    Well, I agree that cusotmer service personal should at least give you an update.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •