Re: Query Optimizer

Gopi,

    The locking or siezing problem that you describe is because when OS/400 goes
to build the acess path, that function does not get interrupted.  That is what 
is
causing the erros (or waits) when you try to access the file, while that index 
is
being created.  Query doesnt always use an existing access path (for various
reasons) when you believe that one does exist.   We have seen this problem many
times before.  We have had great results with our ShowMe/400 product in these
situations.  ShowMe will use the access paths that you give it, and does not 
join
the files together, but will actually "Chain" between the records that it needs
in the files. We have had companies take 3 hour Queries, and run them in ShowMe
in minutes. ShowMe is doing what you would do if you had the time to write all
your queries as programs (you know how quickly your programs perform compared to
Queries).  We will create an inquiry and convert that to RPG and DDS.  If you
want more information, contact me directly or visit us at
http://www.os-solutions.com

Pete Massiello
OS Solutions
Tele (203)744-7854
Fax  (203)790-6056

Gopi Krishna wrote:

> 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
> +---

--
Pete Massiello
OS Solutions International
Phone: (203)-744-7854  Ext 11.
http://www.os-solutions.com
mailto:pmassiello@os-solutions.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
+---