Encoded Vector indexes

[David Boling]

To all:

I read the web page on Encoded Vector Indexes at
http://www.as400.ibm.com/developer/bi/evi.html and it seems as if this
would be the way to go for most web based queries. I can't seem to find
much more information though. Can anyone suggest any other
information. Maybe something with examples. If the response is that
much better it looks like their would be a world of discussion on this
topic but it appears to be a secret. I can create the index but how is
it used.

Thanks for the help in advance.

David Boling

[Tim]

I would also like to point out that in many cases existing EVI's help the
query optimizer decide which index is best to implement a given query, even
if it eventually decides not to use that particular EVI.

Kevin Wright <kevin....@aspect.com.au> wrote in message
news:7u309t$9tl$1...@merki.connect.com.au...
> David,
>
> The same web page should have said that it happens via the query
optimizer,
> that is you can't tell the query to use an EVI, but that the query
optimizer
> can "decide" (if allowed) that using the EVI is the best way to implement
> the particular query.
>
> What this means is that you have to analyze queries yourself to determine
> whether they will benefit from the existence of EVIs. Usual method to do
> this involves the messages issued from the query optimizer while in debug.
>
> I cannot give you any examples, but my impression is that where an EVI is
an
> advantage seems to be where rows that are to be included in the query are
> based on the values of columns that can only have a relatively small
number
> of finite values, eg country, state, status.
>
> Note that this is from theoretical, not practical, knowledge.
>
> HTH
>
> David Boling <boli...@co.rowan.nc.us> wrote in message
> news:3804EA25...@co.rowan.nc.us...

[Kevin Wright]

[David Boling]

This leads me to another question. Are there good tools on the market for
analyzing the OS/400 database in relation to the Query optimizer. It seems that
due to the change in the way business is conducted from OLTP to complex queries
that a index or database is only as good as the ability to use it effectively.
There seems to be very little on the as/400 to analyze or plan the correct
logical files to create, evi or not. I know the route about putting one
session in debug from another session, running the query the first time then
looking at the joblog, but this doesn't seem like a way to analyze a database
that's been around 10+ years. Why spend time and money to develop and new index
method if most of the people using as/400's will never know if it helped or not,
or what is the best index for the sql or queries being developed.

I have never really worked on another of the major database platform, do they
handle database analysis better. Do they offer suggested indexes, etc.

Thanks for the info.

david boling

Tim wrote:

> I would also like to point out that in many cases existing EVI's help the
> query optimizer decide which index is best to implement a given query, even
> if it eventually decides not to use that particular EVI.
>
> Kevin Wright <kevin....@aspect.com.au> wrote in message
> news:7u309t$9tl$1...@merki.connect.com.au...

[Joe]

David, having just returned from Common and attending several sessions
relating to DB2 UDB query optimization I guess I have to respond to this
post. :)
There are no hard and fast rules for query optimization on any platform
since the optimizer is data dependent. You still have to analyze the
recommendations from the optimizer and determine what is best for your
environment. EVI's can't be used to directly access data, ie. CHAIN, READ,
etc. So only build them to improve query access. Otherwise build the
standard indexes. The optimizer debug messages DO suggest indexes, but you
should compare it's recommendations against your existing indexes. As for
tools, Operations Navigator in Client Access Express V4R4 contains a lot of
new database functionality and it will work with previous V4 versions of
OS/400. Goto:
http://www.as400.ibm.com/developer/education/ibcs.html
click Business Intelligence and download the title - Piloting DB2 UDB for
AS/400 with Operations Navigator (Download a self-study LAB only.) to learn
how to use these new features. By the way, a friend of mine who works with
Powerbuilder and SQL Server was at Common and was VERY impressed with the
new database enhancements in DB2 UDB. HTH

David Boling wrote in message <3805CF49...@co.rowan.nc.us>...