Re: Indexes vs Logical Files

[Gavin Inman <midrangelist@xxxxxxxxxxxxxx>]

IBM DB2 Engineer Mark Anderson was the one who told me this.  If a 
physical has keys, it is forced to COE.

On 10/13/2023 9:33 AM, Charles Wilt wrote:

That has never been the case.

And now-a-days, pretty much everything goes through the SQE.

Originally, if you referenced a LF on an SQL statement, you forced the use
of the CQE.

Perhaps that's what you're thinking of.  But again, even that is no longer
the case.


Charles

On Fri, Oct 13, 2023 at 6:52 AM Gavin Inman<midrangelist@xxxxxxxxxxxxxx>
wrote:

> Years ago it was explained to me that if the physical has keys, it's
> forced to use the older database engine.  Unless something has changed,
> I believe that's still true today.
>
> Gavin.
>
> On 10/13/2023 8:47 AM, Greg Wilburn wrote:
>
> I've been using Index Advisor and Run Sql Scripts "Run and Explain" to
> analyze some of our queries.  I'm looking for increased performance.
>
> I have a large file/table OEBOXCBX with a logical files defined with the
> following keys:
>
> LF #1: BXCOM#, BXORD#, BXREL#, BXBOX#, BXPRT#
> LF #2: BXCOM#, BXORD#, BXREL#, BXBXNO, BXPRT#
>
> I ran an SQL statement over a complex SQL view that contains this file.
> RSS Run and Explain recommended the following index:
> CREATE INDEX ASTHHOBJ/OEBOXCBX_IDX
>       ON ASTHHOBJ/OEBOXCBX (BXCOM# ASC, BXORD# ASC, BXREL# ASC) UNIT ANY
> KEEP IN MEMORY NO;
>
> My question is why the SQE wouldn't use one of the existing logical files
> as an index?  As an RPG guy, this makes no sense to me.
>
> TIA,
> Greg
>
> [Logo]<https://www.totalbizfulfillment.com/>      Greg Wilburn
> Director of IT
> 301.895.3792 ext. 1231
> 301.895.3895 direct
> gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
> 1 Corporate Dr
> Grantsville, MD 21536
> www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email:MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit:https://lists.midrange.com/mailman/listinfo/midrange-l
> or email:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> athttps://archive.midrange.com/midrange-l.
>
> Please contactsupport@xxxxxxxxxxxxxxxxxxxx  for any subscription related
> questions.