Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    31

    Unanswered: Fetch First n rows behaviour 9.5 vs 9.7

    In DB2 LUW 9.5 running a query against table 'A' asking for the first 5 rows only always yields the same result set.

    select * from table a
    fetch first 5 rows only;

    Running the same query against a 9.7 database always returns a different result set.

    Note, we don't have an 'ORDER BY' clause and the table in question is static.

    Question, is it sheer coincidendce that 9.5 returned the same result set and 9.7 doesn't or has something changed in 9.7?

    thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In DB2, there is no guarantee of order unless you have an ORDER BY. That has never changed.

    It is possible that you could get away without the ORDER BY in 9.5 in certain situations and get the same results twice, but IBM has always told you that you need it if you want the same results when you rerun the query.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by chippib View Post
    In DB2 LUW 9.5 running a query against table 'A' asking for the first 5 rows only always yields the same result set.
    You were just lucky that all your attempts happened to return the 5 same rows. As Marcus already said, there is no such guarantee.

    Running the same query against a 9.7 database always returns a different result set.

    Note, we don't have an 'ORDER BY' clause and the table in question is static.
    This works as SQL is designed. The problem is that you have a query with not clearly defined semantics, but you imply that there are such semantics.


    Question, is it sheer coincidendce that 9.5 returned the same result set and 9.7 doesn't or has something changed in 9.7?
    Yes, it is just coincidence in 9.5. For example, create another index on the table in 9.5, use that index as clustering index and do a reorg. Chances are pretty good that you get a different set of 5 rows. (Check the access path for such a test to make sure DB2 won't always pick some index like the one supporting the primary key or so.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Is it necessary to specify ORDER BY if the query already has GROUP BY and the table has all the necessary indexes for db2 to use? My mainframe co-workers are in disagreement on whether it's necessary or not, but I think that it's same for LUW and zOS - have to specify ORDER BY to guarantee it's returned sorted.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl View Post
    Is it necessary to specify ORDER BY if the query already has GROUP BY and the table has all the necessary indexes for db2 to use? My mainframe co-workers are in disagreement on whether it's necessary or not, but I think that it's same for LUW and zOS - have to specify ORDER BY to guarantee it's returned sorted.
    Generally DB2 will do a sort in order to satisfy the GROUP BY, so from a practical standpoint it "may" not be necessary. However, there is no guarantee that DB2 will continue to work that way in the future, so an ORDER BY is advised.

    In DPF, the ORDER BY is necessary even if a GROUP BY is present.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Marcus_A View Post
    However, there is no guarantee that DB2 will continue to work that way in the future, so an ORDER BY is advised.

    In DPF, the ORDER BY is necessary even if a GROUP BY is present.
    DB2 10 LUW, which was announced a couple of days ago, has a few new optimizer tricks and employs intra-partition parallelism in some unexpected places on multicore machines that I think can easily change aggregation order.

Posting Permissions

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