http://www-1.ibm.com/support/docview.wss?rs=865&context=SSEPEK&dc=DB510&dc=DB520&dc=D800&dc=D900&dc=DA900&dc=DA800&dc=DA440&dc=D600&dc=D700& dc=DB540&dc=DB400&dc=DA410&dc=DA450&dc=DA430&uid=swg27007905&loc=en_US&cs=UTF-8&lang=all
Preventing table space scans on child tables Forum/discussion group
Abstract
This example SQL statement returns the RI relationships in which the child table has no supporting index. Deleting a row from the parent table results in a table space scan on the child table.
Content
Example:
SELECT DISTINCT F.CREATOR, F.TBNAME AS CHILD,
T1.CARD, F.RELNAME,
R.REFTBNAME AS PARENT,
T2.CARD, R.IXNAME
FROM SYSIBM.SYSFOREIGNKEYS F,
SYSIBM.SYSRELS R,
SYSIBM.SYSTABLES T1,
SYSIBM.SYSTABLES T2
WHERE R.RELNAME = F.RELNAME
AND R.TBNAME = F.TBNAME
AND R.CREATOR = F.CREATOR
AND T1.CREATOR = F.CREATOR
AND T1.NAME = F.TBNAME
AND T2.CREATOR = R.REFTBCREATOR
AND T2.NAME = R.REFTBNAME
AND NOT EXISTS
( SELECT * FROM SYSIBM.SYSINDEXES I, SYSIBM.SYSKEYS K
WHERE F.CREATOR = I.TBCREATOR
AND F.TBNAME = I.TBNAME
AND F.COLNAME = K.COLNAME
AND F.COLSEQ = K.COLSEQ
AND I.NAME = K.IXNAME
AND I.CREATOR = K.IXCREATOR)
Example submitted by: Edward Krisiewicz
Rate this example
User comments on this example:
"I have struggled with this type of query before. I am not sure that the one suggested here really works. It checks for WHERE EXISTS columns of a foreign key in an index of the child table. For each column in the foreign key that exists in an index of the child table, do we know that these columns are in the same index of the child table?"
"This example SQL works in most cases, but not all cases. Here is an abstract example of a foreign key without index that this query would not find.
CREATE TABLE T_PARENT (A INTEGER NOT NULL, B INTEGER NOT NULL, CONSTRAINT A PRIMARY KEY (A, B)); CREATE UNIQUE INDEX T_PARENT_IX ON T_PARENT (A ASC, B ASC); CREATE TABLE T_CHILD (C INTEGER NOT NULL, F INTEGER NOT NULL, K INTEGER NOT NULL, CONSTRAINT C PRIMARY KEY (C, K)); CREATE INDEX T_CHILD_IX1 ON T_CHILD (F ASC, C ASC); CREATE INDEX T_CHILD_IX2 ON T_CHILD (C ASC, K ASC); ALTER TABLE T_CHILD FOREIGN KEY ABFK (F, K) REFERENCES T_PARENT (A, B) ON DELETE RESTRICT ENFORCED; COMMIT; The following works better:
SELECT T.DBNAME
, R.CREATOR,R.TBNAME,R.RELNAME,R.REFTBNAME,R.REFTBCREATOR
, R.COLCOUNT , R.DELETERULE
, R.IXOWNER AS PARENT_IX_OWNER, R.IXNAME AS PARENT_INDEX
, FKI.FK_IXCREATOR,FKI.FK_INDEX,T.CARDF
FROM SYSIBM.SYSRELS R
JOIN SYSIBM.SYSTABLES AS T
ON R.TBNAME=T.NAME AND R.CREATOR=T.CREATOR LEFT JOIN
( SELECT F.CREATOR, F.TBNAME, F.RELNAME
, I.CREATOR AS FK_IXCREATOR, I.NAME AS FK_INDEX
, COUNT(*) AS MATCH_COLUMNS
FROM SYSIBM.SYSFOREIGNKEYS F JOIN SYSIBM.SYSKEYS K
ON F.COLNAME = K.COLNAME
AND F.COLNO = K.COLNO
AND F.COLSEQ = K.COLSEQ
JOIN SYSIBM.SYSINDEXES I
ON I.CREATOR = K.IXCREATOR
AND I.NAME = K.IXNAME
AND I.TBNAME = F.TBNAME
AND I.TBCREATOR = F.CREATOR
GROUP BY F.CREATOR, F.TBNAME, F.RELNAME, I.CREATOR, I.NAME
) AS FKI
ON FKI.CREATOR = R.CREATOR
AND FKI.TBNAME = R.TBNAME
AND FKI.RELNAME = R.RELNAME
AND FKI.MATCH_COLUMNS = R.COLCOUNT
WHERE FKI.CREATOR IS NULL
ORDER BY 1,2,3 ;
P.S. Please pass my address to anyone interested in DB2 HOTLINE - thank you.
With kind regards
Michael Dewert, Software Group
DB2 Development
DB2 Information Management Software
