Re: SQL Optimizer Issue

Did IBM support ask for the detailed optimizer trace? There are settings that I 
do not remember that will put lots more in the job log and, IIRC, into an IFS 
file. But you don't really want to try to understand it. It gives the cost 
estimate stuff in detail that the opimizer used.

Also, have they had you use STRDBMON against the job? This is used by the 
Navigator DB monitors, too, so you could try using those. Or get the raw files 
yourself and analyze them. Go to 

http://www-03.ibm.com/servers/eserver/iseries/db2/db2tips.htm

and look in the performance section  for "Common queries..."

HTH
Vern

-------------- Original message -------------- 

> when they run EXPLAIN over the SEQUEL it ALWAYS says it will use the correct 
> index 
> 
> rob@xxxxxxxxx wrote:No obscure logical file that has a selection criteria 
> specifying plan='B' 
> so it can't use that access path, is there? 
> 
> Rob Berendt 
> -- 
> Group Dekko Services, LLC 
> Dept 01.073 
> PO Box 2000 
> Dock 108 
> 6928N 400E 
> Kendallville, IN 46755 
> http://www.dekko.com 
> 
> 
> 
> 
> 
> Mark Allen 
> Sent by: midrange-l-bounces@xxxxxxxxxxxx 
> 10/14/2005 03:14 PM 
> Please respond to 
> Midrange Systems Technical Discussion 
> 
> 
> To 
> midrange-l@xxxxxxxxxxxx 
> cc 
> 
> Fax to 
> 
> Subject 
> SQL Optimizer Issue 
> 
> 
> 
> 
> 
> 
> Posted for a colleague: 
> 
> What I was told: A SEQUEL select statement is run over a very large 
> (50-60 Million records), select by name and a plan field. MOST of the 
> time results are returned quickly but for "some" names the statement does 
> not use the existing index and thus creates one, which causes about a 
> 20-30 minute response time. IBM support to date has not been able to help 
> and they ahve submitted a PMR to IBM but no response yet and it is 
> becoming (is) an issue. 
> 
> Example: 
> 
> Name=Smith Plan=B, quick response 
> Name=Jones Plan=C, takes forever 
> 
> I was told that they have also tried it as a straight interactive SQL and 
> get the same issue. They have not been able to find any rhyme or reason 
> as to why "some" names/plan combinations take so long. Anyone run across 
> this or have any ideas? 
> 
> 
> --------------------------------- 
> Yahoo! Music Unlimited - Access over 1 million songs. Try it free. 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
> 
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
> 
> 
> 
> 
> --------------------------------- 
> Yahoo! Music Unlimited - Access over 1 million songs. Try it free. 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list 
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
> To subscribe, unsubscribe, or change list options, 
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> or email: MIDRANGE-L-request@xxxxxxxxxxxx 
> Before posting, please take a moment to review the archives 
> at http://archive.midrange.com/midrange-l. 
>