× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.