Hi,
What's left in a view that is not in an index?
- Use of more than one file?
Group by / Having!
Union, Except, Intersect!
Common Table Expressions (including Recursive CTEs)!
Even though indexes can be specified in the F-Specs and used like any other
keyed logical file, the cannot be specified in an SQL-statement. In this way
the new indexes are very valuable.
A view is always used to store complex SQL-Statement and use the in
different places, for example in RPG programs, Java methods, ODBC, Query/400
etc. The physical files (or SQL tables) should never be accessed directly
instead views should be used. A view for each task. Because a view never has
a key you can have "millions" of views without performance decreases. Views
can also be created over views.
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 losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von rob@xxxxxxxxx
Gesendet: Monday, March 03, 2008 22:05
An: Midrange Systems Technical Discussion
Betreff: Re: derived key indexes. end of the CQE?
Way cool. Now you can:
- Give it a record format name
- Specify what fields to include
- Use derived fields (like UPPER(NAME))
- Use selects like DDS (where ....)
What's left in a view that is not in an index?
- Use of more than one file?
Will they finally add something that I've put DCR's out since V1 of i5/OS
and that is a key on a field from more than one file? Sure would be nice
to do:
CREATE INDEX MYLIB.MYINDEX ON
MYLIB.ORDHEAD JOIN MYLIB.ORDDT
ON ORDHEAD.ORDNBR = ORDDTL.ORDNBR
AND ORDHEAD.ACTREC='A' AND ORDDTL.ACTREC='A'
(ORDHEAD.CUSTNBR ASC, ORDDTL.ITEMNBR ASC)
RCDFMT MYINDEXR ADD ORDHEAD.ORDNBR, ORDHEAD.CUSTNBR, ORDDTL.ITEMNBR...
Rob Berendt
midrange.com
