Runstats (Dangerous :) )

[Hattusas]

Greetings.
I am responsible for optimizing our queries which require reasonable sources on the system.
I have a problem about running runstats.

According the usage of the tables I have indexes on various tables.Those indexes work very well and we are able to able significant performance.
However we have to modify the tables frequently so we export our data,drop the table,import,apply the constraints such as foreign keys etc. (or perfom the load action).
Also we take care that while exporting we sort the records
according to the index that we most use.
After that I recreate my indexes and perform these steps.
1. Reorgchk update statistics...
2. Reorg table index <the most important index>
3. Runstats on table ... with distribution and detailed indexes all

After step 2 , I also hit SQL Explain with heavy SQLs and the methodology usually is exactly what I desire.
But sometimes after step 3, (runstats)
visual explain never uses my index again.Also after that,the problem never solves.I recreate my indexes,doing reorg,runstats,... no...The oprimizer refuses to use my query.Subsequently we we run our query,the result is chaos.

I had this problem today and had to give up my process and dropped my db and restored it from back up and made the first 2 steps only.Now it works fast as expected.

Why does runstats crash our job?

Thanks...

Salih Sipahi
Software Engineer.
City of Istanbul Turkey
s.sipahi@sahinlerholding.com.tr

 

[gregsimpson]

Hattusas,

can you explain what you mean by "the optimiser refuses to use my query".

Cheers
Greg

 

[Hattusas]

Hi greg..
When I look at the "explain SQL" section, from the graphics
I see that my index is never used by DB2 in that SQL.
Therefore the result of query becomes worse than before.(I don't make a special command to make DB2 use my index.I only pray DB2 to use my index.But it doesn't )



Salih Sipahi
Software Engineer.
City of Istanbul Turkey
s.sipahi@sahinlerholding.com.tr

 

[gregsimpson]

Hattusas,

from what you're saying, if you don't run the runstats, the optimiser is choosing to use your index, if you do run runstats it does?

If you're getting a worse access path after RUNSTATS than before it's probably worth speaking to IBM, who may have a fix for it already depending on the reasons for a poor access path being chosen.

What version and platform are you on?

Greg

 

[Hattusas]

Greg.
That's exactly what my problem is and for this reason I doubt using runstats when I make certain alterations into my db.

My version is 7.1 but we are going to apply 2 fixpacks (fixpack 3 and fixpack 11) if we get the desired path after applying those 2 fixpacks I will inform the result.
Our platform is AIX platform.

But If I have time I will create the same db into windows enviroment by using db2move command.
Thanks for your response

Salih Sipahi
Software Engineer.
City of Istanbul Turkey
s.sipahi@sahinlerholding.com.tr

 

[atoi]

Have you possibly tried executing RUNSTATS without the WITH DISTRIBUTION clause? Perhaps the data distribution is affecting the optimizer. We have experienced unexpected results in the past when including and/or excluding the clause.

Good luck,

 

[Hattusas]

Worth a try..I will try it without that parameter.I will also retry the command with AND INDEX <myIndex> instead of
FOR INDEX<myIndex>
We will be applying the fixpacks 3 and 11 in 2 days' time.Within a few days I will report what will happen.
Thank you

Salih Sipahi
Software Engineer.
City of Istanbul Turkey
s.sipahi@sahinlerholding.com.tr