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

Hi,

the funny thing is:

DDS described keyed logical files can share access path with an SQL defined
index, if they have less key fields in the same order.
... And they adopt the larger page size of the SQL index.

Birgitta

"If you think education is expensive, try ignorance"
(Derek Bok)

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces+birgitta.hauser=lp-gmbh.com@xxxxxxxxxxxx
[mailto:midrange-l-bounces+birgitta.hauser=lp-gmbh.com@xxxxxxxxxxxx] Im
Auftrag von Wilt, Charles
Gesendet: Dienstag, 2. Mai 2006 16:38
An: Midrange Systems Technical Discussion
Betreff: 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.
> 
>