RE: Query Optimizer -- MIDRANGE-L

The same Seizer problem occurs when we run our batch jobs which uses OPNQRYF
command.

Any suggestion....

Gopi Krishna  :-)
(206) 447 7950 x7319
(206) 344 7319 (direct)

> -----Original Message-----
> From: Danny.Jahr@lawson.com [SMTP:Danny.Jahr@lawson.com]
> Sent: Tuesday, February 23, 1999 11:59 AM
> To:   MIDRANGE-L@midrange.com
> Subject:      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
> +---
+---
| 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
+---