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