Re: how to make a key on a view?

What I usually do is create the index through Ops Nav and then after the
testing is done, right click on the index in Ops Nav and say generate SQL.
I then take the SQL and paste it into a source member which gets checked
into the change management system.


On Fri, Jan 31, 2014 at 1:58 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>wrote:

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.

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