Re: Help solve an argument: Reading a SQL index in RPG?

Hi Rob

Just jumping in here - SQL won't let you use SELECT on an index because it is not a table or a view. That's an SQL standards thing - in SQL, an index cannot be read as if it were a table - in SQL, it IS only usable for optimization, as you mentioned.

Now IBM i is part of a long history, back to the System 38, so far as object-level security and single-level-store are concerned.

The system has always been a relational database system - yes, some will argue, but I'll hold to that, since IBM says so.

Some will say it isn't really, because there is more than one way to work with the database - SQL and native I/O record-level access (RLA).

You can also use SQL views with native I/O - of course, you have no way to get the order you want. IBM i extensions to the standard SQL index DO give you ordering.

I'd say that using indexes like this harkens to an earlier technology, yes, AND looks forward, perhaps. Although if y'all are replacing native I/O with embedded SQL - not sure I go along with that completely, just to be doing it - things could get muddled in the future when you want to define host data structures in which to receive the data from your FETCHes - the set-based natured of SQL doesn't apply so well to interactive screens as it does to mass changes, etc.

All IMHO!

HTH
Vern

On 2/22/2016 9:11 AM, Rob Michaels wrote:

On Mon, Feb 22, 2016 at 8:43 AM, <rob@xxxxxxxxx> wrote:

In this case I would strongly recommend that RPG read the indexes. Why?
Because I have added columns to the underlying table and since the
existing indexes only select certain columns and not a 'SELECT *' the
underlying RPG doesn't get record format level check errors.

But the LF backing the index won't necessarily get the new columns
added. I don't know the exact pattern, but when you do an ALTER TABLE
to add a column to the table, the LF doesn't always get that column
added.

select * from rob.robmindex

ROBMINDEX in ROB not table, view, or physical file.

Yeah, that's my strongest physical argument ... but I'm looking for
something in the IBM docs that indicates WHY SQL won't let you do a
SELECT on an index.

Rob M