Re: CPD4360 - Queried file MYINDEX in ROB not data base file or is an SQL derived key index. WAS: Looking for file structure details

Hi Rob

I agree that "derived" doesn't quite express what is going for me. BTW, I'm giving some thoughts for others - I think you know this stuff.

But there is this setting in QAQQINI - IGNORE_DERIVED_INDEXES which was there to block looking at LFs with select/omit - what we can think of as a sparse index.

But IBM in 7.2 at lease says that a key can be an expression - and that's a derived index.

I think the term is just being used very broadly.

Regards
Vern

On 6/26/2015 12:46 PM, rob@xxxxxxxxx wrote:

CREATE TABLE MYLIB/MYTABLE
(
FIELD1 CHAR (10),
FIELD2 CHAR (10),
FIELD3 CHAR (10),
FIELD4 CHAR (10),
PRIMARY KEY (FIELD1, FIELD2)
)
RCDFMT MYTABLERCD

create index mylib.myindex
on mylib.mytable (field4)
where field3='X'
rcdfmt MYTABLERCD add all columns

When I use RUNQRY FILE(MYLIB/MYINDEX)
against that file I am getting:
QRY1045 - File MYINDEX in ROB cannot be queried.
CPD4360 - Queried file MYINDEX in ROB not data base file or is an SQL
derived key index.

If I drop BOTH the WHERE clause, and the RCDFMT clause, I can then use
RUNQRY QRYFILE(MYLIB/MYINDEX)
https://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajq/rzajqderivedindex.htm?lang=en-us
Normally when I think of a "derived index" I am thinking something like
FIELD1 CONCAT FIELD3 or (annualWage/52).
Apparently using these two clauses gets it thinking it's a derived index.
A WHERE clause should be considered a "sparse" index and not a "derived"
index and therefore should be fat dumb and happy at 7.2.
https://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajq/rzajqsparseindexuse.htm?lang=en-us
Too bad that is not what I am seeing...

Rob Berendt