Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Cluster index on DB2 for Windows does not work

    Hi,

    I have created simple sample and I just found out that cluster index on DB2 for Windows does NOT return correct values (order by cluster). The funny thing is that DB2 on Linux it works fine.

    Sample
    create table db2admin.x (a int not null, b int not null)
    create index db2admin.xc on db2admin.x (b) cluster pctfree 10 minpctused 10
    insert into db2admin.x values (1,2)
    insert into db2admin.x values (3,1)
    insert into db2admin.x values (2,3)


    Results on DB2 v8 fp 5 on Linux S/390 it works fine:

    Code:
    select * from db2admin.x
    A           B
    ----------- -----------
              3           1
              1           2
              2           3

    Results onDB2 v8 fp 6 on Windows XP doesn't sort according to the cluster index:

    Code:
    select * from db2admin.x
    A           B
    ----------- -----------
              1           2
              3           1
              2           3
    Am I doing something wrong or is there a bug in db2 for Windows?

    Thanks,
    Grofaty
    Last edited by grofaty; 07-05-04 at 06:15.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The order of rows returned does NOT indicate the way it is stored in the database ...

    If you wish to have the rows in a particular order, the only supported way is to use and ORDER BY Clause ...


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

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi sathyaram_s,

    But why should I use cluster index if order is not the same? The rows should be inserted into table by order of cluster index. The main purpouse of cluster index is to avoid sorting. I know that this is not 100% and 100% is only if I write ORDER BY cause.

    Why does it works on Linux and not on Windows?

    Thanks,
    Grofaty
    Last edited by grofaty; 07-05-04 at 08:13.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The purpose of clustering the table is to avoid page I/O by putting rows on (or near) the correct page according to the clustering sequence. For example, if an employee table were clustering by dept, the following query would require fewer page I/O's than if the rows were in employee sequence (or randomly spread throughout the table).

    Code:
    select * from employee where dept = "A01"
    When clustering is used during inserts, DB2 only cares about the correct page and not the exact sequence. Obviously, the correct sequence is achieved after a reorg. But a query will never guarantee the correct sequence without an ORDER BY, even if the row are physically in sequence in the table.

    Note that all indexes are always maintained in the exact correct sequence, regardless of whether they are the clustering index or a regular index.

    Since a table can only be clusteed in one sequence (except for MDC, which is a slightly different subject), the trick is find the sequence that will yield the best performance for the most important or most frequent queries. But the cost of maintaining the sequence during inserts also needs to be considered.
    Last edited by Marcus_A; 07-05-04 at 09:50.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thanks to you all. I understand, that ORDER BY is only 100% garantee for correct sequance.

    But why does this works on db2/linux and doesn't work on db2/windows?

    Grofaty

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One possibility is that the query uses the clustering index (which is ordered) on Linux becasue of the default assumptions about the number of rows in the table when the stats are -1. The defaults for table cardinality for DB2 Windows may be different than on Linux. I know that some of the defaults are different between Windows and the other DB2 platforms, such as the default buffer pool size.

    Another possibility is that you may have updated stats, so no index is used. Try the explain.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    I did:
    1. I have droped above table.
    2. Install fixpack 6 for db2/Linux.
    3. Run a script from my first post.

    Result: Now DB2/Linux works just the same as DB2/Windows!!! It looks like the fixpack 6 is the "catch"

    Grofaty

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You do not want any applicaton to rely on the order of rows returned that do not contain an ORDER BY. Regardless of how it works in fixpak 6, it could change in the future.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jan 2003
    Posts
    1,605
    Hi Marcus_A,

    Don't worry, I will include ORDER BY in applications. I just got this question when I was executing a simply query to the table with DB2 Command Window and result was NOT the same on Linux and Windows.

    Thansk a lot Marcus_A,
    Grofaty

    Thanks,
    Grofaty

Posting Permissions

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