Re: Looking for file structure details

On 26-Jun-2015 16:27 -0600, John R. Smith, Jr. wrote:

I'm still in pain and frustrated.

It appears that I can't use this index in embedded SQL in an SQLRPGLE
program or in STRSQL

The INDEX is unsupported for use with DML [including SELECT]. The INDEX is only eligible for use in optimization and possibly also for implementation; for how the query gets optimized, according to the /plan/ developed by the optimizer to get to the data efficiently.

and if I use the TABLE, the debugger [six] appears to ignore the
index because the joblog says it used arrival sequence to build the
select from.

A reader can not divine the query, the CREATE INDEX, nor the other details of the TABLE and data, so the only worthwhile response is effectively: While the debug messages logged by the query optimizer might have suggested for that particular [but unstated] SELECT query, that does not mean that the statistics of the INDEX were unused nor that the INDEX might not be used for a different SELECT query.

Do I have something else wrong or do I need to give up and go back to
my DDS built archive file?

IIRC the earliest SQE support for implementation with the SQL derived-key INDEX [optionally with a WHERE clause] required [and may still] that the query includes the /identical/ derivation in the predicate; the simplest means to identify a compatible INDEX, is to find an INDEX with the matching\identical expression. And conspicuously, the predicate(s) in the WHERE clause of the INDEX must be compatible with the predicate(s) in the query; i.e. the INDEX must not omit any rows the query might select.

Even so, the cardinality of the data or the other predicates of the query [as well as many other possible factors in /optimizing/ a path to the data] may have the query optimizer deem the /cost/ of using the INDEX is higher than an alternate implementation; e.g. more costly than using arrival access path to locate the proper rows.