RE: Can a Query fill the system?

["Mauger, Jenny" <Jenny.Mauger@xxxxxxxxxxxx>]

I think that as query works through SQL it should find the appropriate
logical if there is one without you having to specifically named it.
However, if there is no existing logical for the join you are trying to do
it will dramatically improve performance if you create one.

-----Original Message-----
From: Draper, Dale [mailto:dale.draper@seu.sega.com]
Sent: 02 October 2002 15:31
To: 'midrange-l@midrange.com'
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.