AW: PK's enforced via constraint or unique indexes?

Hi Charles

>>If you have an index, key1, key2, key3 and later create an index key1,
>>key2 the iSeries will not build a separate index, instead it will share
>>the first one.

Sharing access paths in this way is only true for DDS described keyed
logical files.

SQL described indexes will only share access paths if the access path is an
exact match, that means the same columns with the same definition in the
same sequence.

Birgitta

"If you think education is expensive, try ignorance"
(Derek Bok)
 
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Wilt, Charles
Gesendet: Dienstag, 2. Mai 2006 16:00
An: Midrange Systems Technical Discussion
Betreff: RE: PK's enforced via constraint or unique indexes?

Ryan,

DB2 on the iSeries is unique in that it allows the PF object to store an
access path along with the data.  (Remember us going over this a couple
of weeks ago?)

When you define a primary key for a table, the unique index is stored
inside the table object, thus you won't see it separate.

As far as the additional unique index being defined.  It is redundant,
but it doesn't really hurt anything as under the covers the iSeries
implicitly shares the primary key index anyway.  

This "access path sharing" is automatic.

If you have an index, key1, key2, key3 and later create an index key1,
key2 the iSeries will not build a separate index, instead it will share
the first one.


HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx 
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Ryan Hunt
> Sent: Tuesday, May 02, 2006 9:42 AM
> To: midrange-l@xxxxxxxxxxxx
> Subject: PK's enforced via constraint or unique indexes?
> 
> I just want to confirm something about DB2/400...  99 Percent 
> of the tables
> I have on our AS400 are JDE OneWorld tables.  Now, JDE does 
> NOT use Primary
> Key constraints.  Rather, they use unique indexes to enforce 
> integrity.
> 
> Now in the MS SQL world this is a virtual non-difference 
> because Primary
> Keys are enforced via unique indexes anyway.  Typically, the 
> only advantage
> of a PK contstaint is that is shows up with a specfiic type of object
> attribute - so it can be useful in writing code.  And, there 
> might be an
> inherent tendancy for an optimizer to favor PK constraint 
> (indexes) for
> joins (another simple benefit of PK constraints over unique indexes).
> 
> Does DB2/400 act similarly, or is there NO benefit to using a 
> PK constraint
> over a unique index?  Also, if DB2/400 does use unique 
> indexes to enfore PK
> constraints, I should be able see it as a 64K/page index used 
> within the
> "Visual Explain" tool right?
> 
> This question came up because I've got a developer that has 
> created a new DB
> schema.  In this schema each table has a PK constraint defined AND an
> identical unique index defined for the PK columns- which I expect is
> redundant.  Yet, in Operations Navigator I only see the unque 
> index definded
> as a SQL index object and I cannot view the constaint as an 
> object anywhere.
> 
> Thanks in advance.
> 
> Ryan
> 
> 
> 
> -- 
> 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.
> 
>