Can I create an index on a view?

[Shirley]

We are running DB2 on iSeries V5R2.
Using AQUA DATA STUDIO with a connection to our iSeries, I created a
view using SQL and I am trying to create an index on this view using
the code below.

CREATE INDEX reports.Ivendorname ON reports.transbyvendor05
(vendorname)
However I get the following error:
Error: [SQL0156] TRANSBYVENDOR05 in REPORTS not a table.
Is it possible to create an index on a view? If not, what other options

do I have to improve
the performance of my queries on the view I created?
Thanks, Shirley..

[Mark A]

"Shirley" <sm****@mail.gov.gu> wrote in message
news:11**********************@q12g2000cwa.googlegr oups.com...

We are running DB2 on iSeries V5R2.
Using AQUA DATA STUDIO with a connection to our iSeries, I created a
view using SQL and I am trying to create an index on this view using
the code below.

CREATE INDEX reports.Ivendorname ON reports.transbyvendor05
(vendorname)
However I get the following error:
Error: [SQL0156] TRANSBYVENDOR05 in REPORTS not a table.
Is it possible to create an index on a view? If not, what other options

do I have to improve
the performance of my queries on the view I created?
Thanks, Shirley..

No, you can only create an index on a table. When you access a table via a
view, DB2 will may the indexes on the table to process the view (if doing so
is the most efficient access path).

Views do not exit as data that has been materialized. They are just a filter
in front of one or more tables that makes accessing the data more convenient
(such as when the join predicates are in the view so the user/programmer
does not have to do it), or views can be used provide extra security by only
letting users see certain columns or certain rows. Views can also be used to
change column names in query tool that returns the data, and for other
similar reasons.

When you access DB2 via a view, DB2 takes the SQL you have written against
the view, and the SQL in the view, and creates a single SQL statement that
is sent to the optimizer and data-manager for processing.

[Mark A]

"Mark A" <no****@nowhere.com> wrote in message
news:eo******************************@comcast.com. ..

No, you can only create an index on a table. When you access a table via a
view, DB2 will may the indexes on the table to process the view (if doing
so is the most efficient access path).

Views do not exit as data that has been materialized. They are just a
filter in front of one or more tables that makes accessing the data more
convenient (such as when the join predicates are in the view so the
user/programmer does not have to do it), or views can be used provide
extra security by only letting users see certain columns or certain rows.
Views can also be used to change column names in query tool that returns
the data, and for other similar reasons.

When you access DB2 via a view, DB2 takes the SQL you have written against
the view, and the SQL in the view, and creates a single SQL statement that
is sent to the optimizer and data-manager for processing.

Correction to 2nd sentence:

When you access a table via a view, DB2 may use the indexes on the table to
process the view (if doing so is the most efficient access path).