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

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