Hi,
Does anyone have knowledge of side affects of setting IGNORE_DERIVED_INDEX
to *YES permanently?
Access paths stored in DDS described logical files with select/omit clauses
are ignored.
In this way SQL statements that used to use access paths stored in those DDS
described logical files may use a table scan instead of an index access.
That means performance goes down.
A work around would be:
1. Check the Keys used in the DDS described logical files with select/omit
clauses.
2. If there are logical files with the same key, these access paths can be
used by the query optimizer
3. If there are no logical files with the appropriate access path, delete
the DDS described logical file with the select/omit clause. Create a SQL
indexes with the appropriate keys and recreate the DDS described logical
files with the select/omit clauses. In this way the DDS described logical
file will share access path with the SQL index. The SQL index can be used by
the query optimizer, while the DDS described logical file can be used as
before.
BTW SQL indexes can be used like any other keyed logical file with native
I/O.
BTW, was able to remove the S/O's on the offending logical file (this
time) to get the SQLRPGLE program to use the SQE. >>However, we just can't
remove S/O on logical files every time.
Instead of using a DDS described logical file with select/omit clauses in an
SQL statement.
Create an SQL-View where the select/omit clauses are translated into
Where-Conditions and use this SQL view instead of the DDS described logical
file.
With release 6.1 SQL indexes can be enhanced with field selections, where
clauses and format names. These indexes are considered as replacement for
the DDS described logical files with select/omit clauses. That's why with
6.1. IGNORE_DERIVED_INDEXES is set to *YES in the default QAQQINI file in
the QSYS.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"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!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Tyler, Matt
Gesendet: Wednesday, July 09, 2008 00:05
An: Midrange Systems Technical Discussion
Betreff: RE: Use of a Query option in QAQQINI w/o access to QAQQINI
I discussed the option of creating a permanent copy in QUSRSYS with my
manager. He was concerned with possible side affects of just "throwing the
switch" on.
Does anyone have knowledge of side affects of setting IGNORE_DERIVED_INDEX
to *YES permanently?
BTW, was able to remove the S/O's on the offending logical file (this
time) to get the SQLRPGLE program to use the SQE. However, we just can't
remove S/O on logical files every time.
Thanks, Matt
-----Original Message-----
From: Alan Campin [mailto:Alan.Campin@xxxxxxxxxxxxxxxx]
Sent: Monday, July 07, 2008 3:02 PM
To: midrange-l@xxxxxxxxxxxx
Subject: RE: Use of a Query option in QAQQINI w/o access to QAQQINI
<snip>
I would like to have the option IGNORE_DERIVED_INDEX set to *YES for regular
end users that do not have authority to QAQQINI to CRTDUPOBJ into QTEMP.
The offending logical file is related to my SQLRPGLE only by the fact I
joined a file that is part of a SQLVIEW that happens to
join to the offending logical file's table.
</snip>
You could make it permanent by creating a copy of QAQQINI into QUSRSYS and
pointing your system to use that copy and changing the value on
IGNORE_DERIVED to yes. This is how I have it done in our system so that it
will always ignore derived indexes. This is going to be the default in the
next release.
I, also, have a user defined function at www.think400.dk/downloads.htm to
set query options at run time. This could easily be complied to run under
owner authority but that would mean that any query option could be set. I
don't know if that would be a problem or not. Since it is a program object
underneath, you could authorize the program to only certain users.
Hope this helps.
midrange.com
