Indexes on RI

Ruediger Kurtz

Indexes on RI
Hi anyone out there,

I have a question concerning indices on foreign keys in our shop.
We automatically generate indices on foreign keys. A survey on
SYSIBM.SYSPACKDEP has shown that quite a number of those indices is not
being used by packages.
Now we're wondering whether we can drop those indices without any harm
or whether those indices will be used by DB2 internally to check on RI,
especially when deleting rows from dependent tables.

TIA Ruediger Kurtz

[login to unmask email]

Re: Indexes on RI
(in response to Ruediger Kurtz)
If DB2 Internally uses any of the parameters or the objects for its
internal operation then IBMREQD for that Row will be 'Y'.
So ,if u can find any row with IBMREQD as 'Y' then u cann't /shouldn't
delete that row or drop that particular index otherwise as i think u are
free to determine and drop the redundant indexes.

I hope this serve ur purpose..

Sanjeev





[login to unmask email] on 10/08/99 09:45:59 AM

To: [login to unmask email]
cc: (bcc: SANJEEV SETHI/ITS - Tata Steel)
Subject: Indexes on RI




Hi anyone out there,
I have a question concerning indices on foreign keys in our shop.
We automatically generate indices on foreign keys. A survey on
SYSIBM.SYSPACKDEP has shown that quite a number of those indices is not
being used by packages.
Now we're wondering whether we can drop those indices without any harm
or whether those indices will be used by DB2 internally to check on RI,
especially when deleting rows from dependent tables.
TIA Ruediger Kurtz

Michael Hannan

Indexes on RI
(in response to ssethi@LOT.TATASTEEL.COM)
Ruediger,

F.K. Indexes are used for R.I. checks on Delete of Parent row.
If not available T.S. scan used.

Can drop F.K. index if not used and Parent row delete is not allowed.
We drop them on large tables where parent is small reference table with very
static rows (almost never deleted).

From: Michael Hannan

>From: [login to unmask email]
>Organization: HUK-Coburg
>Subject: Indexes on RI
>To: [login to unmask email]
>
>Hi anyone out there,
>
>I have a question concerning indices on foreign keys in our shop.
>We automatically generate indices on foreign keys. A survey on
>SYSIBM.SYSPACKDEP has shown that quite a number of those indices is not
>being used by packages.
>Now we're wondering whether we can drop those indices without any harm
>or whether those indices will be used by DB2 internally to check on RI,
>especially when deleting rows from dependent tables.
>
>TIA Ruediger Kurtz
>
>