Re: Query Optimizer

If you CHGQRYA QRYTIMLMT(0) you can run the optimization
and it will end before the query actually runs.

Also, the system is building an access plan the first and second
time the query runs which takes longer.

Dan Jahr





pytel@us.ibm.com on 02/23/99 12:43:08 PM

Please respond to MIDRANGE-L@midrange.com
                                                              
                                                              
                                                              
 To:      MIDRANGE-L@midrange.com                             
                                                              
 cc:      (bcc: Danny Jahr/Lawson)                            
                                                              
                                                              
                                                              
 Subject: Re: Query Optimizer                                 
                                                              








There's not much you can do, if optimizer chooses to build access paths -
for big files it takes time.
The key to this problem is to understand why optimizer wants an access path
of it own.
Run your query in debug mode and look at optimizer messages in a joblog -
there could be a clue.
You cannot tell optimizer what to do, but you can influence its decision
indirectly - for example, ordering on specific keys may force optimizr to
choose existing access path.
There are some query parameters you can tweak, also you may try to
reformulate your query with the same result, using your knowledge of data
semantics.
Another approach is to try to speed up access path builds - on AS/400 there
are some parallel query features which can improve this process, depending
on spare processing power you may have on your system.

You may send me more information (offline) and I will have a look if smth
can be done: query definition, PFs and LFs definitions, query joblog in
debug mode.

Best regards
    Alexey Pytel



Gopi Krishna <gkrishna@starbucks.com> on 02/23/99 09:41:50 AM

Please respond to MIDRANGE-L@midrange.com

To:   "'midrange-l@midrange.com'" <midrange-l@midrange.com>
cc:    (bcc: Alexei Pytel/Rochester/IBM)
Subject:  Query Optimizer





We are experiencing problems with Query Optimizer. Here is the problem.

We are joining to big PFs with over 14 millions records (size >4GB each).
We
have lots of LFs on these 2 PFs. After defining the query, when we try to
save the definition and select opiton 2 to run in Batch, it takes atleast
20
minutes to save the definition & submit it to the batch. I know why its
happening. Its Building an Access Path. Even though we have Access paths
that could be used, Optimizer is not using.  While the Query/400 is saving
the definition if I try to browse one of the files used in the query
through
EZVIEW (DB tool) it doesn't allow me to get it, because the file is locked
(but it never gives lock msg). Even if we run interactive jobs (Payroll)
using one of these 2 files, samething happen and the job is hanging until
the query save is complete. During this lock, If do SYS REQ 3 and try to
run
option 11 (Program Stack), it takes longtime before it displays the empty
stack.
I contacted IBM, they have not solution to this. They are asking to purge
these files. We don't want to do this. So they asked us to increase the
Access Path Size to 1TB for the PFs/LFs. Since the PF is not keyed I can't
increase it for PFs but I did for LFs. No improvement.

Any idea what could be done to fix this problem?

Thanks

Gopi Krishna  :-)

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---



+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---







+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---