RE: Query Optimizer -- MIDRANGE-L

Thanks.

But when we do similar query using OPNQRYF in CLs we get the same condition
and its siezing.
Also, I got around the Query/400 problem by breaking the join into 2
different query. That helped to finish the query in 5 minutes instead of 30
minutes.

Gopi Krishna  :-)

> -----Original Message-----
> From: Pete Massiello [SMTP:pmassiello@os-solutions.com]
> Sent: Tuesday, February 23, 1999 8:50 AM
> To:   MIDRANGE-L@midrange.com; gkrishna@starbucks.com
> Subject:      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
+---