Vern/Birgitta/Elvis
Thanks for clarifying the distinctions between indexes, logical files and
access paths. I've noticed that many people use the terms interchangeably. I
seem to remember being told that DDS logical files can share indexes (rather
than access paths). That was on a DDS DB course albeit awhile ago now.
Also, for me, the implementation differences between SQL and DDS are very
useful as I use both. Thanks for the informative link, Vern.
Cheers
Keith
-----Original Message-----
From: Vernon Hamberg [mailto:vhamberg@xxxxxxxxxxx]
Sent: 20 December 2007 17:58
To: Midrange Systems Technical Discussion
Subject: RE: SQL descending primary key
Keith
I think your terms are a little reversed - indexes have access paths,
not the other way around. An SQL index is implemented as a logical
file. Perhaps you mean something different when you use the word
index. Access path is an attribute of all LFs and PFs and tables and
indexes (and maybe even views, although they are merely encapsulate
SELECT statements and have no persistent access path that I know of).
Some are arrival sequence, others are what we've learned to call
keyed. Files share access paths, not the other way around, I believe.
I'm probably not quite right on this, but an access path is
implemented using a data space that seems like a list of key values
with pointers to the actual records. This is actually a separate MI
object and hence can be shared by multiple LFs - except that SQL
doesn't seem to, as Elvis points out. And Elvis knows he can correct
me in any of this. ;-)
I just read some of an article by Dan Cruikshank, where he says that
SQL indexes will not share a DDS keyed LF's access path. But it also
says that access paths in SQL indexes can be shared - not sure if
that includes constraints and primary keys and all. The link to the article
is
http://www-03.ibm.com/servers/eserver/iseries/db2/pdf/Performance_DDS_SQL.pd
f
Here's the list of how things work now--
1. An SQL index will not share a DDS keyed logical file's access path
2. A DDS keyed logical file will share an access path created via SQL
3. A DDS keyed logical file will share an access path if the keys are
a subset of the key
fields in the existing access path and in the same order. For example
LF1 with keys
K1 and K2 will share an access path that is keyed on K1, K2 and K3.
4. An SQL index will only share another SQL created access path if
the key columns
are an exact match in the same order.
5. A DDS multi-formatted logical file will not share existing access paths.
6. A DDS join logical file can share existing access paths
7. The DYNSLT file level keyword can be used to cause DDS logical
files containing
select/omit criteria to share an existing access path
Regards
Vern
At 10:06 AM 12/20/2007, you wrote:
i wouldn't...i can use the table as is in RPG, etc. why add the extra
overhead for an identical key??
I agree I wouldn't (see above). However, there should be no additional
overhead. The two indexes should share a common access path.
I think that it's the indexes are shared, where possible, between access
paths rather than the other way round.
For example, an access path has 3 key fields in order: A, B and C. Creation
of this access path will result in 1 index. If a second access path is then
required with 2 key fields A and B then, on creation, will use the existing
index. So we have two access paths and only one index. However, if the two
access paths were created in reverse order then we'd have 2 indexes with
the
associated overhead.
Cheers,
Keith
This e-mail was scanned by SurfControl MailControl in accordance with the
Hastings Direct Inbound policy
Help cut carbon...please don't print this email unless you really need to
Hastings Insurance Services Ltd trading as Hastings Direct is an independent intermediary, authorised and regulated by the Financial Services Authority. Registered in England No: 3116518. Registered Office: Conquest House, Collington Avenue, Bexhill-on-Sea, East Sussex, TN39 3LW.
CONFIDENTIALITY. This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the named recipient, please notify the sender immediately at mailto:postmaster@xxxxxxxxxxxxxxxxxx and do not disclose the contents to another person, use for any purpose, or store or copy the information in any medium. Please note that any views expressed in this message are those of the individual author and do not necessarily represent those of Hastings Insurance Services Ltd.
This email has been scanned by Mailcontrol from Blackspider.
midrange.com
