Re: SQL Optimizer Issue

After upgrading from V5R2 to V5R3, we experienced the same problem as you
described. Query manger reports that use to take about a one minute to run
were taking about 5hrs. After two months of going back and forth with IBM,
we discovered the issues was on how Select/Omit logical files were handed in
the new Query Optimizer engine (SQE vs CQE). Anyway, the fix was to add a
new entry into the QAQQINI file so it would ignore select/omit logical
files. 

IGNORE_DERIVED_INDEX  set to *YES

<vhamberg-Wuw85uim5zDR7s880joybQ@xxxxxxxxxxxxxxxx> wrote in message
news:<101420052230.4281.4350318900091B02000010B92202888744099D0A0D030E0890@c
omcast.net>...
> 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-tksaTn4SAz0AvxtiuMwx3w@xxxxxxxxxxxxxxxx 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-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx 
> > 10/14/2005 03:14 PM 
> > Please respond to 
> > Midrange Systems Technical Discussion 
> > 
> > 
> > To
> > midrange-l-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx 
> > 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-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx 
> > To subscribe, unsubscribe, or change list options, 
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> > or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx 
> > 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-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx 
> > To subscribe, unsubscribe, or change list options, 
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> > or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx 
> > 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-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx 
> > To subscribe, unsubscribe, or change list options, 
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
> > or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx 
> > 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-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request-Zwy7GipZuJhWk0Htik3J/w@xxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> 
>