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:


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

Am I doing something wrong or is there a bug in db2 for Windows?

Thanks,
Grofaty

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

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

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).

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.

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

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.

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

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.

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