set current explain mode recommend indexes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

db2admin - 07 Jul 2006 22:52 GMT

hi,

our db2advis is broken and i am trying to find other ways to get
recommended indexes
i tried using command "SET CURRENT EXPLAIN MODE RECOMMEND INDEXES" and
it populated ADVISE_INDEXES table. new indexes recommended by this
command does not have runstats on it.
how can i evaluate new suggested indexes ?
do i have to physically create these indexes and then explain my SQL ?
can i do it without actually creating suggested indexes ?
what is the difference between "SET CURRENT EXPLAIN MODE RECOMMEND
INDEXES"
and "SET CURRENT EXPLAIN MODE EVAULATE INDEXES"
they both seems to fill same kind of information in ADVISE_INDEXES
table.
please suggest me a way to get around functionality of db2advis

regards,
jag

Reply to this Message

Mark A - 08 Jul 2006 01:00 GMT

> our db2advis is broken

This is a blessing in disguise.

> please suggest me a way to get around functionality of db2advis

Hire someone who understands DB2 performance tuning and can tune the query
with using db2advis.

Reply to this Message

db2admin - 10 Jul 2006 19:48 GMT

> > our db2advis is broken
>
[quoted text clipped - 4 lines]
> Hire someone who understands DB2 performance tuning and can tune the query
> with using db2advis.

thanks for your reply mark but you did not answer my question

Reply to this Message

Mark A - 10 Jul 2006 21:42 GMT

> > > our db2advis is broken
> >
[quoted text clipped - 6 lines]
>
> thanks for your reply mark but you did not answer my question

Sorry, I meant "without" using db2advis.

db2advis will not give you the best optimization of your system. It
often suggests creating indexes that are not needed. It also seems to
(inexplicably) puts columns in descending order when suggesting
indexes, for no discernable reason.

Reply to this Message