RE: Using the Index Advisor -- MIDRANGE-L

Just save the DDL you use to create the index in QDDSSRC.

You'll developers will find it if they need to.

Otherwise, they'll never know it's a DDL index instead of a DDS logical file.

Charles Wilt
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Mike Cunningham
Sent: Wednesday, March 12, 2008 3:47 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Using the Index Advisor

True from the technology side. No so true from the multiple developer side
and everyone knowing when, where and how to deal with an index with no DDS
source and an LF. Recreating the LF gets the best use of the index and
keeps everyone on the same page, just the database designer needs to know
both worlds. At least during the transition phase to SQL while standards
and rules are worked out and until retraining can happen

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of MKirkpatrick@xxxxxxxxxxxxxxxxx
Sent: Wednesday, March 12, 2008 3:43 PM
To: Midrange Systems Technical Discussion
Subject: RE: Using the Index Advisor

or, I believe that you could just create the index and use that in your
HLL pgm. No need to create a LF.

midrange-l-bounces@xxxxxxxxxxxx wrote on 03/12/2008 12:30:56 PM:

So this might be one of those instances where I could consider
deleting the LF, creating the SQL index and then recreating the LF
so it shares the SQL index?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Wednesday, March 12, 2008 3:21 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Using the Index Advisor

Query optimizer is a funny animal. I don't think anyone can tell you

with

full certainty why it does some of the things it does.

I can tell you for sure it does "care" about keyed LFs!

One reason for redundant advice may be that having an SQL index with

larger

logical page size (64K vs LF's 8k) might bias query optimizer toward

using

that index. This is especially helpful in situations where index is

being

scanned sequentially (i.e. read-by-key, read-next-key, next....). Both
native I/O program can do this as well as query engine.

This is by no means the only "quirk" query optimizer has. I'm sure

you'll

run into more as you dig deeper into it.

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and

OS/400

www.centerfieldtechnology.com

-----Original Message-----
Subject: Using the Index Advisor

I was reviewing the Index Advisor in iSeries Navigator today and see

entries

advising me to create multiple indexes that I confirmed already exist

via a

logical file. The logical file has existed for years in some cases yet

the

advisor shows today as the last advised for query use date. Am I reading
this wrong or does the Index Advisor not care about DDS indexes?

--
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 email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
Unless you are the addressee (or authorized to receive for the addressee),
you may not use, copy or disclose to anyone this email or any information
contained in this email. If you have received this email in error, please
advise the sender by replying to this email, and delete this email
immediately. Please note that any views or opinions presented in this
email are solely those of the author and do not necessarily represent
those of Western Dental Services, Inc. Finally, the recipient should
check this email and any attachments for the presence of viruses. Western
Dental Services, Inc. accepts no liability for any damage caused by any
virus transmitted by this email.
--
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 e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.