Dear all,

Since I' m an Oracle DBA I am familiar with Index Organized Tables. (IOT)
The table is in fact a btree structure , the leaf blocks/pages contain the data (whereas a "normal" btree index contains a rowid, a pointer to the actual table data)
I thought this was available in DB2 as well, I think it was called btree tables.
Can you confirm me this ?
Any documentation links ?

Regards
Guy Lambregts

There is no such tables in DB2 (or its analog).
There is only ORGANIZE BY KEY SEQUENCE. But I doubt it appropriate for you.

and clustering index ? would that help..?
This tries to keep the data that belongs together (organized by this index) on the same pages..

An index organized table is really just a index with all the columns included in it. You can do that in DB2, but obviously if there is a lot of inserting or deleting, then there is some extra overhead of inserting/deleting the actual row into the table in addtion to the index (but the index overhead is much higher because it must be in exact sequence). If all the columns are in an index, DB2 will almost always use index only access for select statements.

Usually, defining a clustering index is the best solution, however the table will require reorgs on a periodic basis to maintain a reasonable clustered sequence if you have a lot of inserts. Careful coordination of percent free and reorg frequency is also advised.

With a clustering index defined, DB2 tries to insert rows on the correct page, but never reorders rows on a page for the exact sequence the way an index is ordered. If there is no room on the correct page, DB2 will not split the page (like an index would) and just puts the row on a nearby page, or any page if no space is available on a nearby page.

MDC (organize by) is a reasonable alternative, especially in a decision support environment. This is similar to table clustering in Oracle, but with only one table.

Hi all,

Thanks for your valuable advice, I really appreciate it since my DB2 knowledge and experience is limited and since I' m involved these days in a DB2 database design.
To be honest I have some DB2 deceptions :
1. Oracle offers us these Index Organized Tables (IOT) the data is the index, the index is the data. I fully agree these tables are slower with regard to insert/update performance, but reduce logical/physical reads.
2. Oracle offers us as well the multiple table index clusters, tablerows of multiple tables can be stored in the same datablock / page.
3. Oracle offers us as well multiple table hash clusters, tablerows of multiple tables can be stored in the same datablock / page.
4. The clustering index of which was spoken here above seems to be very close to the single table hash cluster, in Oracle we have as well the sorted hash cluster, seems also available in DB2.

There are offcourse strong DB2 features as well.

Regards

DB2 has several clustering solutions as well, which don't wxactly match the way Oracle does it. There are pros and cons of each method.

If you post a specific example of a database design, someone will recommend the best DB2 clustering solution for you.