RE: compare using qry or logical

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

It takes a long time to find something that is not there. It is probably 
doing a full table scan to determine this fact. I think an index on the 
ARBGRP field (any index - LF or SQL index - where that is the first key 
field, or an EVI index - SQL - on that field should speed things up.

You can see what is going on by using STRDBG (no program), which will put 
optimizer messages into your joblog. Also, run CHGQRYA QRYTIMLMT(0) to get 
the chance to cancel the OPNQRYF process - the messages will still be in 
the log. Prompt CHGQRYA to see what the current value is.

These message include what kind of access path was used and why, and 
recommendations for indexes that might help.

When done, ENDDBG and CHGQRYA QRYTIMLMT(*SYSVAL) to put things back. The 
system value is probably *NOMAX.

As far as performance on maintaining LFs, it depends. One key factor is how 
often it needs to be maintained. If the PF is fairly quiet, no problem. If 
it is getting lots of activity (writes, updates, deletes), then there will 
be more of an issue.

HTH
Vern

At 10:56 AM 10/6/2004, you wrote:
> 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.