Re: Sql Table / View / index issue

At least the messages in this part of the thread is on topic for this list :-)

I had already alluded to the new SQL derived INDEX in another message. However I had not contemplated its use for anything other than read-only access\IO. I wonder to what lengths the SQL INDEX goes to mimic the keyed DDS LF? Since SQL can not name the INDEX for DML, it would be irrelevant to the SQL if the file was marked as read-only. If the logical file created for the INDEX was created as read-only, would of course be _very pertinent_ to RLA. What are the ALWUPD, ALWDLT, and ALWINS [made-up kwd, to describe allow insert capability] capabilities of a SQL INDEX that includes a column defined as the result of an expression? If Allow Update is *NO, then what %FIELDS might hope to accomplish, is moot.

I was unaware that there was a %FIELDS feature. IIRC the database will implicitly prevent I/O to both input-only and neither fields; the neither fields implicitly, as they are not even made available\visible to the program. As such there would seem to be no requirement for the %FIELDS, with regard to omitting fields that are not update capable. Is there any situation requiring the %FIELDS builtin function on the UPDATE of database I/O, prior to the new SQL derived index? For example, is the %FIELDS perhaps required for an RPG UPDATE issued on a VIEW which has a column defined to be the result of an expression?

If %FIELDS is required to update a VIEW or an INDEX, by excluding a column that is derived from an expression, then what is the I/O error that prevents the UPDATE when %FIELDS is not used?

Regards, Chuck

rob@xxxxxxxxx wrote:

I wonder if you could do that on one of the new 6.1 indexes,
providing you specified the %fields option on the update so
that it didn't try to update the udf?

Bruce Vining wrote:

One scenario that arose recently at one of my accounts was the
desire to do keyed IO from RPG (using common data management
interfaces) to a view containing a UDF.

CRPence wrote:

Doug Palme wrote:

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

No. What scenario gives rise to that inquiry? <<SNIP>> 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.