The approach to clustering is different between SQL Server and DB2.

As most SQL people know, SQL Server will always physically insert a new row in the correct place in a cluster index. This is because the bottom-level leaf pages of a SQL Server cluster index are also the data pages.

DB2 has 2 types of cluster index, both of which work differently to cluster indexes in SQL Server.

The original DB2 cluster index (written in the 1970s) most closely compares to a SQL non-clustered index on a table with no cluster index. In other words, the bottom level of the cluster index just has RID pointers into the table. When a row is inserted DB2 will place it in the first available slot, with the index updated as normal. DB2 keeps a statistic called clustered% to show how close the actual physical order of rows is to the clustered index definition. Most DB2 DBAs would rebuild a cluster index when the cluster% drops below 95%. When a DB2 cluster index is rebuilt, the table rows are sorted into the sequence of the cluster index. Immediately after a DB2 cluster index is rebuilt, both the index and the data are in the same physical sequence. There are advantages and disadvantages of the DB2 approach compared to the SQL Server approach.

DB2 Multiple Dimension Clustering (MDC) indexes work in a different way. See my previous post in this thread for an introduction to the MDC concept, which came into DB2 about 2001. When a new row is inserted in a table with a MDC index, it is stored in the correct extent for the key entry. In this way, a MDC index is self-maintaining, and a MDC index rebuild is only necessary when it is desired to reclaim space.