Now I've changed the primary key of the table to (type_id, product_id)
while making (type_id) as MDC.

When examing the plan, I found that the following query:

             select client.client_id, sum( prod.quantity * prod.price
* client.ordersize )
             from prod, client
             where prod.type_id = 4 and prod.product_id =
client.product_id
             group by client.client_id

does use the new primary key on (type_id, product_id).  However, if I
use a bind variable for type_id, i.e.:

             select client.client_id, sum( prod.quantity * prod.price
* client.ordersize )
             from prod, client
             where prod.type_id = ? and prod.product_id =
client.product_id
             group by client.client_id

It only uses the MDC on (type_id).

Now, my questions are:

1) should I drop the bind variable on type_id so it can utilize the
primary key?  The cardinality of type_id is about 5 to 8.

2) Would an addition on (product_id) help?

Thanks.

Hi,
there is a shell script know extent utilisation.sh which can be
downloaded from IBM website.it will answers all your queries regarding
which will be a better column and how much space will you save and how
will it improve the performance.

When a table has a clustering index, DB2 will *try* to insert rows into
the table in order to keep the data in the same order as the clustering
index.  However, there is no guarantee that this will happen, because it
depends on there being enough free space on the particular page DB2
wants to write the row.

If there isn't enough free space on the "correct" page, DB2 will insert
the row elsewhere, thus reducing the cluster ratio.  [Incidentally, this
is why MDC is so nice:  It guarantees that the table remains clustered.
Not only that, it allows you to effectively have multiple clustering
indexes].

In your case, when you start with an empty table (i.e. after you
truncate it at the beginning of each day), the cluster ratio is going
to be a mess very quickly.  Remember, INSERT ignores the table's
PCTFREE setting.  Say you have inserted 5 rows, which fill up 1 entire
page.  If the 6th row you insert logically belongs on that first page,
but that page is already full, your cluster ratio is dropping quickly.

As I said before, unless you insert your data in sorted order, the
cluster ratio will not be good until you perform a reorg.  Until you
have enough pages in the table (each with sufficient free space to
accommodate newly inserted rows), your cluster ratio will drop quickly.

Finally, regarding sorts:  It should be obvious that because the rows in
the table are not guaranteed to be in order, a sort operation must take
place if your query uses only a table scan.  A query that scans the
clustering index will fetch the rows in sorted order, and depending on
the cluster ratio this may or may not be very efficient.