Results 1 to 11 of 11

Thread: DB2 Neophyte

  1. #1
    Join Date
    Feb 2006
    Posts
    3

    Unanswered: DB2 Neophyte

    Hi everybody and first of all sorry my bad english

    The customer i work for has just migrated from DB2 MVS to DB2/AIX64 8.2.2 (same version) and we noticed that while select on MVS without explicit "order by" clause were sorted according the primary key, now in AIX the select are sorted in random way. Not only, every time we refresh the select the sort order is always different.
    We noticed also that the problem is not limited where there's "order by" absence but also where the sorting is not explicited for all the fields of the table.
    Ex.
    MyTable has 4 fields :A, B,C and D

    If i run following statement: select * from Mytable order by A,B
    Every time i refresh the select we have random sorting on the other 2 fields not included into the order by.
    At the moment the only way i have to avoid this is to explicit the "order by" clause for all the fields.

    There's a way (db side) to force to change this unpredicatable way of sorting avoiding rewriting every sql statements?

    Anyone can help me?


    Thanx in advance


    Simon

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Select without an order by or group by clause will not sort your results. This includes the mainframe. The rows are sometimes retrieved in the order that they are stored. On the mainframe, you most likely encounterd the rows in order of the primary key because they were physically stored that way. Probably because the PK index was the clustering index. You will have to add the order by or group by clause to sort your results.
    Last edited by urquel; 02-27-06 at 17:51.

  3. #3
    Join Date
    Feb 2006
    Posts
    3
    Hi urquel ,
    i was thinking the same thing but why every time i refresh the select the sort order is different?
    As u sad the rows should be retrieved ALWAYS in the order that they are stored but now here is not like this and in mainframe we didn't have this problem

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    it is not necessary that the rows are retrieved in the order in which they are stored ...

    The only way to gurantee the order of the rows is by using an ORDER BY clause .. Of course, the ORDER BY is an expensive operation

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2004
    Posts
    330
    I did not say that the rows are ALWAYS retrieved in the order that they are stored. I was saying that they probably came back that way because of how they are stored, and that can not always be guaranteed. In other words, DB2 got the rows for you and they happened to be in order(the rows are not sorted), but not because they will alway be in that order. It's just a phnenomenon that occurs with a clusteing index. The only way to guarantee the order of your results in with an order by/group by clause which tells DB2 to sort.

  6. #6
    Join Date
    Feb 2006
    Location
    Utrecht, Netherlands
    Posts
    16
    Well, that should be easy, should it not?
    "while select on MVS without explicit "order by" clause were sorted according the primary key"
    I assume you have a primary key on your DB2 table. Use that order! This will prevent DB2 from using a temporary table for sorting.
    If that is not what you want, then your primary key differs from the primary key as it was on MVS. Should it be? I hope not.

    cheers,
    Rob.

  7. #7
    Join Date
    Oct 2005
    Posts
    109
    I am not sure, but what I see here in this shop:

    for DB2 MVS the index is usually a clustered index, so the data will be stored also according to that (and probably also returned that way). Primary keys are only defined for RI.

    On distributed systems (DB2 on AIX), the index is a 'normal' index and defined as primary key, without clustering.

    I can imagine, that this makes a difference.

    Also: did you check the access path, whether the host uses some join method with an implicit sort and the other system does not ?
    Juliane

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Juliane - Thanks for pointing out the difference in clustering index order between zos and luw.

    For the op , the main point remains valid 'You cannot gurantee the row order if you have no ORDER BY Clause'. GROUP BY is only for grouping and does not gurantee ordered results

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Feb 2006
    Posts
    3
    I've to thank to all of u for the answers,

    till now the only way to solve the problem is to put an explicit "ORDER BY".

    We tried also to rebuild a clustered index but with none result :-(


    Simon

  10. #10
    Join Date
    Oct 2005
    Posts
    109
    Quote Originally Posted by sathyaram_s
    For the op , the main point remains valid 'You cannot gurantee the row order if you have no ORDER BY Clause'. GROUP BY is only for grouping and does not gurantee ordered results
    Sathyaram, Simon, of course that is very true. I just added my thoughts.
    Juliane

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    simon71, can you post the SQL 'create index..' statement?
    There is posible you don't have anought PCTFREE parameter defined. Maybe you also need reorg of index...

Posting Permissions

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