RE: SQL descending primary key

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

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