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

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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