Re: EVI recommendation

I don't think "embedded index" is an official term, but given that PF
object on the i can have both an index space and a data space unlike other
DBs...I thought it made sense.

If you use DDL to create SQL indexes, they should already have the 64K page
size. If you used DDS, the on the CRTPF or CRTLF, you can change
the PAGESIZE parm. Note the max is 512K.

Chuck Pence has made multiple posts countering, or at least providing food
for thought against, Dan's idea to move wholesale to DDL. I wasn't able to
find the post I was thinking about in a quick search, maybe Chuck will jump
in here. :)

Charles




On Mon, Nov 25, 2013 at 3:58 PM, Gary Thompson <gthompson@xxxxxxxxxxx>wrote:

Thanks Charles,

I think Rob was also a little suspicious.

Sys admin has been threatening some ptf's
someday soon but . . .

I built several of the recommended, but held off
on the one you mention for reasons similar to yours
and then cleared the advisor for that schema and
am now thinking that particular EVI was an old hint ?

Anyways, I've yet to see that EVI return to the list.

Interesting thought "embedded index" - new to me.

But, you reminded me of a recent note on page size so
I think I may want to re-make the indexes I made today
to get 64K page size ?

I just now looked at Dan Cruikshank's iProDev article
from Apr 30, 2005 and he stopped just short of a
blanket recommend.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Monday, November 25, 2013 1:29 PM
To: Midrange Systems Technical Discussion
Subject: Re: EVI recommendation

There's no performance difference, AFAIK, between a separate index and a
embedded one really. Assuming you create both with the same page size.
DDS defaults to 8K, DDL to 64K (IIRC).

You certainly wouldn't want an EVI over a unique key (or PK ) of a table.
I'm surprised index adviser would ask for it. Are you up to date on PTFs?
Are you sure you're not misreading it? For instance, an EVI on some
transaction file that references the table in question, ie. has a foreign
key reference to the 1.3M record file, might be reasonable.

Personally, I'd be interested to know what IA would say when the table has
an actual PK defined instead of the DDS unique key.

Charles


On Mon, Nov 25, 2013 at 1:27 PM, Gary Thompson <gthompson@xxxxxxxxxxx

wrote:

I am working on top Index Advisor recommendations and have a recommend
to build an Encoded Vector Index on a field which is declared as the
single unique key to a file used daily for several applications.

This file was created some years ago using DDS and has about 1.3
million rows.

My questions:

1: To meet the Index Advisor recommendation, would it be a
good idea to create an SQL index on the recommended field/column, or
better to re-create the DDS file with DDL ?

Lazy programmer wants to just add an index, but diligent DB admin
thinks "pure" solutions give better performance ?

2: An MC Press article by Kevin Forsythe January 25, 2004 seems to
say EVI's are better "where there are few distinct values", but this
file has over a million distinct values, so lazy programmer thinks
this is a deal-breaker ?

Lazy programmer thinks this may be supported by October 31,
2013 developerWorks article: "Accelerated analytics - faster
aggregations using the IBM DB2 for i encoded vector index (EVI)
technology, but lazy programmer also thinks Index Advisor is his
friend ?
Note: the second paragraph of the Overview seems to support Kevin
Forsythe's advice.

--
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.