Re: Can a Query fill the system?

Dale-

I was always under the impression that you should specify the file with the
least number of records FIRST in the join specifications.

However, I recently saw a post by Kent Milligan (DB2/400 guru at IBM) on
comp.sys.ibm.as400.misc that indicates the query optimizer will take care of
this for you, so it doesn't matter which file you specify first.



----- Original Message -----
From: "Draper, Dale" <dale.draper@seu.sega.com>
To: <midrange-l@midrange.com>
Sent: Wednesday, October 02, 2002 9:30 AM
Subject: RE: Can a Query fill the system?


> While we're on the subject, which is better performance wise, having the
> smallest file as the primary joined to a larger file, or visa versa?
> I remember being told this many moons ago.
>
> And another bottom feeders question, performance wise, am I better off
> querying a logical file than the primary? I expect yes, but ignorance is
not
> bliss.
>
>
> -----Original Message-----
> From: Steve Landess [mailto:steve_landess@hotmail.com]
> Sent: Wednesday, October 02, 2002 7:21 AM
> To: midrange-l@midrange.com
> Subject: Re: Can a Query fill the system?
>
>
> When running Query, OPNQRYF, or SQL there is the possibility that the
system
> will create a COPY of one or more of the files used while performing query
> in order to improve performance.
>
> This happens mostly, if not exclusively, when joining files.
>
> There are ways to deal with this:
>
> With CRTSQLRPG, you can specify ALYCPYDTA(*NO) to prevent this.
> With OPNQRYF, you can also specify ALWCPYDTA(*NO) to prevent this.
> With STRSQL,  you can also specify ALWCPYDTA(*NO) to prevent this.
>
> I'm not quite sure how to prevent it when using AS/400 Query (RUNQRY).
>
> I'm sure that one of the other rocket scientists here <grin> can probably
> tell you how to keep this from happening...
>
>
> ----- Original Message -----
> From: <pam@medisense.com>
> To: <midrange-l@midrange.com>
> Sent: Wednesday, October 02, 2002 8:57 AM
> Subject: Can a Query fill the system?
>
>
> >
> > I'm running a Model 720 with 400 GB, which is usually about 75% full.
Last
> > night, it suddenly went to 90%. When I dialled in to check it, the only
> > suspicious job I could find was a query that had been running for over
> three
> > hours and still hadn't created output. So I killed it, and like magic, I
> was
> > back at 75%.
> >
> > Now that I've had a chance to look at yesterday's performance graphs, I
> find
> > that three times, this query and similar ones filled the disk to the
> > threshhold in the course of two hours. These queries link four files,
one
> of
> > which is huge. Clearly, there's something wrong with the joins. I've
seen
> this
> > sort of thing cause queries to run for ages to no effect, but I've never
> seen
> > them chew up disk space like that.
> >
> > What could it be doing that would fill 60GB in 2 hours?
> >
> > Pam Phillips
> >
> >
> > _______________________________________________
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > To post a message email: MIDRANGE-L@midrange.com
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> > or email: MIDRANGE-L-request@midrange.com
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
> >
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>