Re: Indexes vs Logical Files -- MIDRANGE-L

Vern,

Actually, as I remember it...
IBM originally recommended referencing an LF, yeah it forced the use of
CQE, but it also was a "hint" if you will for what access path to use.
Then as the SQE got better, they switched to recommending that you not
reference an LF.

But that changed with the release of 7.1 ...

As of 7.1,

Currently, the dispatcher routes an SQL statement to CQE if it finds that
the statement references or contains any of the following:

INSERT WITH VALUES statement or the target of an INSERT with subselect
statement
tables with Read triggers
Read-only queries with more than 1000 dataspaces, or updatable queries with
more than 256 dataspaces.
DB2® Multisystem tables
multi-format logical files
non-SQL queries, for example the QQQQry API, Query/400, or OPNQRYF

Since 7.2

Currently, the dispatcher routes queries to SQE unless it finds that the
query references or contains any of the following:

INSERT WITH VALUES statement or the target of an INSERT with subselect
statement
Tables with Read triggers
Read-only queries with more than 1000 dataspaces, or updatable queries with
more than 256 dataspaces.
Db2® Multisystem tables
QQQQry API

In earlier releases (7.3-), for other non-SQL queries, for example
Query/400® or OPNQRYF, the routing of the query could be controlled by the
QAQQINI SQE_NATIVE_ACCESS option. SQE_NATIVE_ACCESS is no longer supported
(at 7.4+) and a non-SQL query will always run SQE unless unless it contains
one of the conditions listed above.

HTH,
Charles

On Fri, Oct 13, 2023 at 10:00 AM VERNON HAMBERG Owner via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Of course, it is highly recommended that one does not specify an LF in a
SELECT (unless occasionally for Select-Omit) - the optimizer will just go
out to the PF and see what the indexes are for it, then choose what it
thinks best.

On Select-Omit - those are in the class of things called derived-key
indexes. I've looked around some on this QAQQINI setting - recent
documentation doesn't even include IGNORE_DERIVED_INDEX. The default at
first was *NO, then it was changed to *YES at 6.1 of the OS. Maybe these
days, this setting is ignored - someone else would need to day. Of course,
we now can define indexes to include a WHERE clause - analogous to S/O LFs
- how do those work with SQE? This is a sandbox I've not played in.

Cheers
Vern

On Fri, 13 Oct, 2023 at 8:34 AM, Charles Wilt <charles.wilt@xxxxxxxxx
<mailto:charles.wilt@xxxxxxxxx>> wrote:

To: midrange systems technical discussion

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
<mailto: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/><

https://www.totalbizfulfillment.com></a>> Greg Wilburn

Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx
<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:

gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>

1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com><

http://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<mailto:

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<mailto:

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:

support@xxxxxxxxxxxxxxxxxxxx> for any subscription related

questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:
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<mailto:
MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:
support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
--
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
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.