It is the query optimizer who decides if or which access path (Index or
logical file) is used when executing a SQL Statement.
Specifying an index in a SQL Statement is not allowed, i.e. you can only
specify physical files, tables, views (and logical files which you SHOULD
NOT do).
If a logical file is specified within a SQL Statement, the query optimizer
will rewrite the SQL Statement by analyzing the DDS description. It will
rewrite the SQL Statement based on the underlying physical file(s) or SQL
Tables in composition with the Field Selection, Join and SELECT/OMIT clauses
(translated into SQL).
After having rewritten the SQL-Statement Optimization start, by considering
ALL available access paths.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
DEnglander--- via MIDRANGE-L
Sent: Thursday, 14 September 2023 15:55
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: DEnglander@xxxxxxxxxxxxxxx
Subject: SQL Index question
There is a SQL Index on our system in our Production library. It has a Last
Used Date of today [it seems to be used every day].
There is no source for its creation in our normal places, so it appears to
have been created on the fly in a SQL window [not a problem].
My question is how to find out what is causing the Last Used Date to be
updated. I have searched all source program files and nothing is referencing
it, so I feel comfortable saying that it's not an RPG nor CL program. I am
under the impression that any other language on the system [i.e. Java, PHP,
etc] can only access DB2 using SQL [as opposed to using Native methods].
Using SQL to access columns in this index is not permitted [gives SQLSTATE
42851]. Any way for me to find out what is using it or causing the Last Used
Date to be updated?
When I look at SYSINDEXSTAT, I see that the LAST_QUERY_USE field and
LAST_STATISTICS_USE field is also today. Is it correct to assume that if
either of these fields get updated, then the Last Used Date field gets
updated too?
Thank you,
Doug
"CONFIDENTIALITY NOTICE: This e-mail transmission (and/or the attachments
accompanying it) contain confidential information belonging to the sender.
The information is intended only for the use of the intended recipient. If
you are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution or the taking of any action in reliance on
the contents of the information is strictly prohibited. Any unauthorized
interception of this transmission is illegal under the law. If you have
received this transmission in error, please promptly notify the sender by
reply e-mail, and then destroy all copies of the transmission."
--
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.
midrange.com
