Re: how to make a key on a view?

Alan

I concur on the use of the index advisor. However, I don't suggest that anyone have the indexes created there.

For many of us, we have change management processes in place, and letting the system build the indexes would likely go against the control and management of change we need to follow.

That was probably an obvious thing to say, still, obvious is not always so obvious.

:)
Vern

On 1/31/2014 2:44 PM, Alan Campin wrote:

The better way to do all this is to use Operations Navigator. Don't put any
indexes on a table unless they are used by other programs.

Go into your reporting tool and run your reports multiple times. When you
are finished, open up Ops Nav and go to the Database section and make sure
that the schema PROJ_MKWN1 is on your list.

Display the list of tables and find the DTAWHS table. When you do, right
click on the table name and select the index adviser will show a list of
indexes it thinks you need to add. You can also get a combined list. What
you are looking for are indexes that are being built by the OS again and
again. You can then click on an entry and ask the system to create the
index.

The reason that you want to do this is that what you think is the index you
want may not be the index the system needs and it automatically generates
the scripts to create the indexes.

I don't know if this is all way beyond your knowledge level but you should
build indexes based on the system recommends rather than trying to guess.


On Fri, Jan 31, 2014 at 7:20 AM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

On 1/30/2014 11:40 PM, CRPence wrote:

On 30-Jan-2014 15:36 -0800, Hoteltravelfundotcom wrote:

On 30-Jan-2014 14:58 -0800, Hoteltravelfundotcom wrote:

the source for the DTAWHS is this:
CREATE TABLE PROJ_MKWN1/DTAWHS (
<<SNIP>>
INVOICE# CHAR (8 ),
<<SNIP>>

I wanted to index the invoice number <<SNIP>> This new table has the
invoice amount data which they wish to add to the report.

So... Ignoring the subject, what seems to be desired, is satisfied by
the following SQL statement that will create an INDEX to provide a keyed
Access Path over the data in the Invoice Number column.?:

CREATE INDEX PROJ_MKWN1/DTAWHSINV# ON PROJ_MKWN1/DTAWHS (INVOICE#)

...and after you create the index, the query tool (Crystal Reports?)
will refer to the base table, DTAWHS, not to the index. The index is
for the use of the optimiser.
--buck
--
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.