RE: Query Optimizer

This may or may not help, but we have found that both Query and SQL will NOT
use access paths if any of the keys in the view (table, LF etc.) are concat
fields.  We do this a lot with date fields (CC YY MM DD) etc. as well as
others and have researched this problem and found that it was the
concatenated fields.  If we build a physical field and tried it, it worked
like a champ.  No amount of specifying of the view would work.

We have a couple of solutions to this that can reduce the impact to existing
programs.  If you would like it, let me know (e-mail directly) and I will
share what we do and you can see if it works for you.

John

John Bussert
jbussert@stecnet.com
www.stecnet.com
Swift Technologies, Inc.
847-289-8339
847-289-8939 fax

> -----Original Message-----
> From: owner-midrange-l@midrange.com
> [mailto:owner-midrange-l@midrange.com]On Behalf Of Gopi Krishna
> Sent: Tuesday, February 23, 1999 9:42 AM
> To: 'midrange-l@midrange.com'
> 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
+---