Very simple statement:
select distinct locid from location

The table contains a million of rows, and the statement only return 1-3 records (i.e. the table only have 1-3 sets of locid). Index (locid) is built in the table, it takes 3-7 seconds to run this SQL, very slow, I have tried to build clustering index on locid, it helps a little, but still very slow, take 2-5 seconds to run. Is there any way to improve the performance of this SQL?

Thx in advance.

Are you assuming that the duration of a query should be related to the queries output in terms of rows returned?
2-5 seconds does not seem unreasonable. I have a 14 second wait for a similar query fetching 4 distinct years from a 2.5 million table.

However query optimization is set at 9, which may be okay for complicated queries and bad for simple ones (Optimizer is given more time to choose an access path)

In order to satisfy the query, DB2 has to read all the rows of the index. Even though the column length of the index may be small, 4 million is a lot of index rows to read.

Most studies have shown that optimization level 7 is about as high as you want to go except in very rare cases. Optimization level 9 can take a lot longer than level 7.

You have only one option - build a summary table. You will have whole set of new issues like maitaining the table(when to refresh), but it's a different story.