Re: Sql Table / View / index issue

Just create another LF or index on the key fields you will search by - the same ones in the SELECT/OMIT - it will find the records very quickly - you don't need the S/O to speed things up.

I'm not sure if an S/O logical can share an access path, but if it can, you could create the 2 logicals in a certain order, so that they can do that.

HTH
Vern

-------------- Original message --------------
From: "Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx>

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" wrote in message
news:...

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

of

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

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.