Question on the design advisor

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

datapro01@yahoo.com - 16 Mar 2005 20:33 GMT

Running DB2 8.1.1 on AIX 5.1

When I run the design advisor on selected sql I am getting
'recommendations' for indexes which already exist. I don't understand
why this is happening. They are described in the recommendations
exactly as they already exist. Relatively new to DB2.

Not sure if this is normal but don't understand, yet, why it would be.

Thanks
Gerry

Reply to this Message

Ian - 17 Mar 2005 17:12 GMT

> Running DB2 8.1.1 on AIX 5.1
>
> When I run the design advisor on selected sql I am getting
> 'recommendations' for indexes which already exist. I don't understand
> why this is happening. They are described in the recommendations
> exactly as they already exist. Relatively new to DB2.

If I'm thinking of the same section in the advisor output, this is
just where the advisor points out which (existing) indexes it found
useful. Any other existing indexes were not useful for the workload
that you presented.

Reply to this Message

datapro01@yahoo.com - 24 Mar 2005 20:13 GMT

Question: Do you have to have a toold db to use design advisor though
the control center?

Reply to this Message

Pierre Saint-Jacques - 24 Mar 2005 21:20 GMT

No you don't. You have to have the Advise Tables built.
If your explain tables have been built, quite likely the Advise ones are
there also.
If not, look in ..\sqllib\misc There's a file called EXPLAIN.DDL
Edit it to choose in which tablespace of that db you want your eplain/advise
tables; connect to the db and run the file.

You're now set up to get explained and advised.
HTH, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

> Question: Do you have to have a toold db to use design advisor though
> the control center?

Reply to this Message

datapro01@yahoo.com - 01 Apr 2005 14:28 GMT

Pierre:

The problem we are having now is that we can do a db2advis on the
command line against a query and get results (recommendations). If I
then query explain_instance and/or advise_index those tables do get
populated.

However if I try running the same query through the Control Center GUI
Design Advisor I don't get any recommendations at all. I also noticed
that when I run the query through Design Advisor the explain/advise
tables do not get populated.

Have an ETR open with IBM on this but so far no luck.

Thanks
Gerry

Reply to this Message