AW: Setting a key field on a view -- MIDRANGE-L

can't one create an Index over a View?

No, an index can only be created over a SQL Table or a DDS described
physical file.
An index is nothing else than an access path and it's the query optimizer
that decides if an access paths must be used and which one.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and loosing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jerry Adams
Gesendet: Tuesday, 23. September 2008 20:05
An: Midrange Systems Technical Discussion
Betreff: RE: Setting a key field on a view

Birgitta,

I haven't done any Views before so excuse me if this is a silly question,
but can't one create an Index over a View? I thought I read this somewhere
(probably the senility kicking in again).

Jerry C. Adams
IBM System i Programmer/Analyst
B&W Wholesale
office: 615-995-7024
email: jerry@xxxxxxxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Tuesday, September 23, 2008 12:28 PM
To: 'Midrange Systems Technical Discussion'
Subject: AW: Setting a key field on a view

nope...until v6.1 views are unkeyed.

Even in release 6.1 views cannot have any key.

In release 6.1 SQL indexes now can have where conditions (just as
select/omit clauses in DDS described logical files)
It is also possible to add new (calculated) fields to an index.

CREATE INDEX MySchema/MyIndXX ON MySchema/MyTable
(Year(MyDate) AS DATEYEAR ASC, Upper(Col2) as UpperCol2 ASC, Col3
Desc)
WHERE Status in ('01', '07', '22', '47');

These indexes can be used by the query optimizer for executing SQL
statement, but they also can be used like any DDS described logical with
native I/O.
Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and loosing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx
Gesendet: Tuesday, 23. September 2008 17:43
An: Midrange Systems Technical Discussion
Cc: Midrange Systems Technical Discussion; midrange-l-bounces@xxxxxxxxxxxx
Betreff: Re: Setting a key field on a view

nope...until v6.1 views are unkeyed. in order to do a "chain" to the data
in the view you'll have to use embedded SQL.

Thanks,
Tommy Holden

From:
Mike <koldark@xxxxxxxxx>
To:
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date:
09/23/2008 10:40 AM
Subject:
Setting a key field on a view
Sent by:
midrange-l-bounces@xxxxxxxxxxxx

I am creating an SQL view for a file that strips out the spaces in a
particular field. My question is if there is a why to set a key on that
new
view so a person can still CHAIN the file. We are on V5R3.

--
Mike Wills
Midrange Programmer/Analyst

Sick of corporate radio and hungry for something new?
http://thenextgenerationofradio.com
Stalking me? http://twitter.com/MikeWills |
http://friendfeed.com/mikewills
--
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.