RE: compare using qry or logical -- MIDRANGE-L

Build a logical that is always there - not on the fly. Don't have any S/O specifications, just the logical. Use the STRDBG and optimizer messages to see what is going on. Then use the same OPNQRYF statement - it'll probably use the new logical to find that nothing is there.

You should also look at the OPTIMIZE parameter, which can help the optimizer make better choices. A value of *FIRSTIO and a small number of records will bias things toward using an index. A large number of records biases it toward table scan. I think *MINWAIT tends toward index use, as well, don't remember for sure.

So for the one value you could use 1 for # of records, 100,000 for the other, say.

HTH
Vern

At 11:02 AM 10/6/2004, you wrote:

In that case, I recommend trying the logical.  That should definitely save
you the 30 minutes.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

"Dwayne Allison" <Dwayne.Allison@xxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
10/06/2004 10:56 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc
Andy Tran <Andy.Tran@xxxxxxxxx>
Fax to

Subject
RE: compare using qry or logical


What we have is a program that run Monday through Thursday nigh within our
cycle.  This program is called twice.  The first with the OPNQRYF
FILE((PASPBR)) QRYSLT('ARBGRP *EQ "*R*"') and then with the OPNQRYF
FILE((PASPBR)) QRYSLT('ARBGRP *EQ "*C*"'.  We have records that equal *R*,
but none in the file that equal *C*.  I can go along with it taking a
while to process the records with an *R*.  But if we have no *C*, why is
it taking thirty minutes to process.  It should read the file and find no
record are there and end.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of rob@xxxxxxxxx
Sent: Wednesday, October 06, 2004 10:47 AM
To: Midrange Systems Technical Discussion
Subject: Re: compare using qry or logical


There are various speed implications here.
1)  The speed of the actual generation of the OPNQRYF versus the actual
generation of the logical file, or view.
2)  The speed of access using the file via OPNQRYF versus the speed of
access using the logical file.
3)  The sum total of 1 & 2 done via OPNQRYF versus using a logical file.
4)  Any performance implications of leaving a logical file out there might

have on database updates.  (I have a 1998 copy of the AS/400 Experts
journal Volume I, Number I on my desk.  It has an article in there by Skip

Marchesani in which he states that it is a MYTH to believe that "logical
files on a file are resource intensive, have a negative impact on
performance, and should only be used when absolutely necessary".  He goes
on to state that IBM cleaned this up on V2 of OS/400 and even better in V3

of OS/400.  Others say they have testing that disputes Skip's statements.)

If 4 doesn't prove to be a concern in your environment then item 1 drops
out of the picture if you leave the logical file out there.  This will, in

turn, shorten item 3.  But if you're deleting/rebuilding the logical file
all the time you're probably losing any performance improvement.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

"Dwayne Allison" <Dwayne.Allison@xxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
10/06/2004 10:30 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
compare using qry or logical


We are using the following command:


OVRDBF     FILE(PA0PBR) TOFILE(PASPBR) SHARE(*YES)
OPNQRYF    FILE((PASPBR)) QRYSLT('ARBGRP *EQ "*C*"')

Is it quicker to create a logical file with a select ARBGRP that equal *C
only or use the above query?

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.