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.



This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].