Re: AW: IGNORE_DERIVED_INDEX

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Rob,

The only issue you may run into...

Queries that used to be forced to the CQE because select/omit logicals
existed on the table are now processed by the SQE and for one reason
or another SQE is slower for a particular query...mainly a query which
was making use of a DDS select/omit logical.  The CQE could use them
the SQE can't.

My understanding is that for example,  JD Edwards product had lots of
queries that fell into this group.  The initial work around at 6.1 was
to set IGNORE_DERIVED_INDEX back to *NO.  But the actual fix was to
create true derived indexes using SQL DDL that could be used by the
SQE.

I'd recommend making an SQL Plan Cache snapshot before making teh
change so you can look for any adversely affected queries.

HTH,
Charles



On Mon, Feb 14, 2011 at 12:54 PM,  <rob@xxxxxxxxx> wrote:
> Where can I find this recommendation?
>
> I am as big a fan as the next guy for DDL but if I have a thousand LF's
> supplied by my vendor I am in no position to convert them all to DDL from
> DDS.  Is there a problem changing IGNORE_DERIVED_INDEX without doing the
> DDS to DDL convert?
>
>
>
> Rob Berendt
> --
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>          Garrett, IN 46738
> Ship to:  Dock 108
>          6928N 400E
>          Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
> To:     "'Midrange Systems Technical Discussion'"
> <midrange-l@xxxxxxxxxxxx>
> Date:   02/14/2011 11:55 AM
> Subject:        AW: IGNORE_DERIVED_INDEX
> Sent by:        midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> The recommendation is to use IGNORE_DERIVED_INDEX *YES and create all your
> logical files (except the join logical files) as SQL indexes.
> With release 6.1 it is possible to create derived and sparse indexes which
> allow to create new columns, define keys over these new columns and add
> where clauses to an index.
>
> Because SQL indexes can be used with native I/O like any keyed logical
> file
> and indexes can be used by the SQE, there is no need to create and use
> logical files with select/omit clauses.
>
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von rob@xxxxxxxxx
> Gesendet: Monday, 14. February 2011 17:21
> Betreff: IGNORE_DERIVED_INDEX
>
> What is the recommendation for "IGNORE_DERIVED_INDEX" in the QAQQINI file?
> We are running 8.3 03 of Infor LX on IBM i 7.1.
>
> I noticed that the 6.1 Memo To Users has this:
> QAQQINI option changes
> In V6R1, the default value for the QAQQINI option for IGNORE_DERIVED_INDEX
>
> has changed from *NO to *YES. The default behavior now runs supported
> queries through the SQE even if a select/omit logical file index is
> created over any of the tables in the query. In V6R1, the SQE optimizer
> supports many types of derived indexes. The usage of the QAQQINI option
> for IGNORE_DERIVED_INDEX applies to only select/omit logical file indexes.
>
>
> Rob Berendt
> --
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>          Garrett, IN 46738
> Ship to:  Dock 108
>          6928N 400E
>          Kendallville, IN 46755
> http://www.dekko.com
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.