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

Birgitta,

Thanks for the correction.  I didn't realize that the SQL indexes worked 
differently.

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 Hauser, Birgitta
> Sent: Tuesday, May 02, 2006 10:28 AM
> To: 'Midrange Systems Technical Discussion'
> Subject: 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.
> > 
> > 
> 
> -- 
> 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.
> 
> -- 
> 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.
> 
>