AW: CQE, SQE and QAQQINI

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

there will be no problem when creating the indexes first and delete and
rebuilt the logical files after. 
But keep in mind you are creating additional access paths (because an SQL
index cannot share access path with an existing DDS described logical file!
And additional access paths may decrease the performance when inserting,
updating or deleting records in the base tables, because each time all
access paths built over this file must be actualized.

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 Jim Essinger
Gesendet: Friday, 28. August 2009 20:58
An: Midrange Systems Technical Discussion
Betreff: Re: CQE, SQE and QAQQINI

Thanks Rob,

My question has more to do with logistics and potential down time.  If I can
build the indexes prior to kicking everyone off the system, that would save
some time.  I can do indexes anytime during the day/week, and then be up
less at night on the weekend to delete and rebuild the logical files and do
testing to make sure that programs are not affected.

If, on the other hand, the existing DDS logical files cause issues with the
building of the new indexes, I have to do it all (delete DDS LF, build SQL
index, recompile DDS LF) when I have the system dedicated, and thus take
more of my weekend sleep time.

I get cranky if I don't get my 7hours!

Jim



On Fri, Aug 28, 2009 at 12:22 PM, <rob@xxxxxxxxx> wrote:

> Think I've heard here that if you build the index first and then the
> logical then the logical will be better because it uses a different page
> size or some such animal.
>
> Now, keep in mind that if you have a Select/Omit logical that says select
> records with an active record code field (IID for example on the item
> master in bpcs) and then orders by item number it shouldn't use an index
> just by item number.  You will need an index by active record code and
> then item number.
>
> Remember:  "Where" trumps "order by" every time.
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:
> Jim Essinger <dilbernator@xxxxxxxxx>
> To:
> Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Date:
> 08/28/2009 01:51 PM
> Subject:
> Re: CQE, SQE and QAQQINI
> Sent by:
> midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> Is it important to delete the logical file first?  Can you build the
> index,
> then delete and recreate the logical?  Does the existence of the logical
> impair the proper creation of the index?
>
> This is a very informative thread!  Thanks all
>
> Jim
>
> On Fri, Aug 28, 2009 at 7:39 AM, Birgitta Hauser
> <Hauser@xxxxxxxxxxxxxxx>wrote:
>
> > The main problem may be that there are access paths (for example in
> logical
> > files with select/omit clauses) that are currently used by the CQE but
> > ignored by the SQE.
> > In this way SQL statement that perform well in the current environment
> will
> > run very slowly.
> >
> > The best way is to search your logical files with keys and select omit
> > clauses. Delete the logical files, create indexes for the key
> information
> > and recreate your logical files. In this way SQL/SQE can use the new
> > indexes
> > and native I/O can use the DDS described logical files. Deleting and
> > recreating the logical files is necessary, because a logical file can
> share
> > access path with an SQL index, but a SQL index cannot share access path
> > with
> > a logical file (because of the larger default page size).
> >
> > 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 Jonathan Mason
> > Gesendet: Friday, 28. August 2009 14:54
> > An: midrange-l@xxxxxxxxxxxx
> > Betreff: CQE, SQE and QAQQINI
> >
> > We have an SQL statement that uses the CQE and runs extremely slowly. In
> > test I have created an Encoded Vector Index and also changed a local
> > version
> > of the QAQQINI file so that the IGNORE_DERIVED_INDEXES is set to *YES.
> >
> > With these settings the SQL runs extremely fast, however the powers that
> be
> > are nervous about changing a global setting that would potentially
> affect
> > all SQL requests.
> >
> > I've spent the morning talking to my friend, Google, and searching
> through
> > the Midrange.com archives, and everything I have seen indicates that
> using
> > the SQE is better than using the CQE.
> >
> > With this in mind, are there any pitfalls in changing the
> > IGNORE_DERIVED_INDEXES setting to *YES?  Should we expect all SQL to run
> at
> > least as fast as does currently or is there a risk that some SQL
> processes
> > could run slower?
> >
> > Thanks in advance
> >
> > Jonathan
> >
> >
> >
> > --
> > 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.
>
>
>
> --
> 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.