AW: Sql Table / View / index issue -- RPG400-L

Hi,

in release 6.1. SQL will check the derived indexes and know about the where
clauses or new fields you created.
But the SQE optimizer will not use DDS described logical files with
select/omit clauses.
BTW the default value for IGNORE_DERIVED_INDEXES in the QAQQINI-File get
changed in release 6.1. from *NO to *YES.
That means by default logical files with select/omit clauses now are ignored
while optimizing the query.

Even though SQL indexes cannot be specified in an SQL-Statement, SQL indexes
can be used in the RPG F-Specs like any other keyed logical file.
Native I/O will profit the most from the new derived indexes.
You can almost replace all DDS described logical files with either derived
indexes (for keyed access) and views (for non-keyed access).
You only need DDS to create keyed joined logical files (with or without
select/omit clauses).

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 loosing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Lim Hock-Chai
Gesendet: Thursday, 04. September 2008 16:03
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: Sql Table / View / index issue

Uuummm,
Chuck only said that SQL DDL, in V6R1, allows where clause when create
index file. Does that also means that SQL engine in V6R1 will know to
use the access path of index with where clause? Does SQL engine also
know to use access path of LF with select/omit in V6R1?

"Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx> wrote in message
news:<mailman.8469.1220536648.2545.rpg400-l@xxxxxxxxxxxx>...
Chuck,
Our OS in not at v6r1 yet. But the main reason I would need Index Over
View is to allow fast SQL access to records in file that meet certain
criteria. As you know, SQL engine does not know to use LF access path
if the LF contains select/omit. I guess, from your reply, my issue with
this is resolved in V6R1.

"CRPence" <CRPbottle@xxxxxxxxx> wrote in message
news:<mailman.8406.1220493549.2545.rpg400-l@xxxxxxxxxxxx>...

Doug Palme wrote:

Is there any way to create an index over a view?

No. What scenario gives rise to that inquiry? Nothing apparent,
with regard to RPG. With more information about the scenario, a

better

response may be forthcoming, directed at solving the problem instead

merely a yes|nor answer to the question.

What about creating an SQL INDEX instead of a VIEW. Perhaps the
following satisfies whatever gave rise to that question?

The DB2 for i [with IBM i 6.1] offers some new function with its

SQL

CREATE INDEX statement. That new function enables an SQL INDEX to be
created both with mapping [expressions represented as columns] and
selection [a WHERE clause], very similar to what a DDS LF derived
[keyed] [select/omit] index might provide. For lack of a column list,

the SQL INDEX can not always\easily be made to match, to replace, an
existing DDS LF, but the RCDFMT clause is enabled. Refer to the
enhanced SQL CREATE INDEX syntax:

http://publib.boulder.ibm.com/infocenter/systems/topic/db2/rbafzxcindx.h
tm

See also:

"Derived key index"

http://publib.boulder.ibm.com/infocenter/systems/topic/rzajq/rzajqderive
dindex.htm

"Using derived indexes"

http://publib.boulder.ibm.com/infocenter/systems/topic/rzajq/rzajquseder
ived.htm

Regards, Chuck

----------