Re: Sorting records in Physical and Logical files

Hi Pete

Just had another distoybing thought! In some RDBMS' I've worked with other than the i, indexes cannot be used for SELECT statements as one of the files. Only tables and views. Now mySQL seems to allow ORDER BY in its views, hence, those would be like what we think of as keyed LFs. I don't think this is an SQL standard - the i certainly does not allow it, not sure about SQL Server or Oracle. mySQL does tend to add some "vendor"-specific extensions.

Now on the i, I do believe that you can use indexes in F-specs in RPG, with the K set. So therein lies the murk or murks today!

Vern

-------------- Original message --------------
From: Pete Helgren <Pete@xxxxxxxxxx>

Thanks Vern,

Yeah, I know that SQL is a different beast. When I access a file through
SQL I always specify an "order by" if the sorting is important. What
was throwing me was I didn't realize that DDS only defined file access
for *RPG* programs (as long as you used a "K" in the F spec). I just
assumed (always dangerous) that the physical file was ordered by the key
order in the definitions and that logicals were "views" in a real
sense. It just never occurred to me that DDS wouldn't apply to files
that were accessed through other means than RPG.

If the key order specified in the DDS for logical *does* affect the way
records are presented outside of an RPG program (I'll have to try this)
, then that seems really inconsistent to me. e.g. if key order doesn't
apply to physicals but does apply to logicals when the files are
accessed outside of RPG then that seems inconsistent.

Anyway, most of what I write these days is SQL in RPGLE not the
traditional record access of chains and reads. But this is good stuff
to remember the next time I get confused....

Pete


vhamberg@xxxxxxxxxxx wrote:

Pete

Query/400 is not the same as SQL - yes, it did use, I think, the same engine

under the covers as the classic query engine. So it probably does honor the
order of a logical file - but I've not tested that in forever - never use
Query/400 queries anymore. I always us QM queries, which ARE SQL.

OTOH, you do have an option on a QRYDFN to specify a sort order - maybe an

comparison test is in order.

I also believe that if you specify a logical on an F-spec and leave off the K,

it'll be in the arrival sequence of the PF. Is that right?

Now SQL knows nothing about this thing called a logical file - it knows views

and indexes. The iSeries implements those object types USING a logical file.
That does not mean there is a strong equivalence there as to how they work in
native and how in SQL mode.

Vern