Is it possible to determine the list of child/parent tables for a
particular table from any system catalog tables? Using the
syscat.tables I'm able to retrieve the no of dependent parent/child
tables but unable to determine the list of dependent table name(s)
Example :
For a simple department-employee relationship, the following query
gives the count of parent/child tables but not the table names(s).
db2 => select tabname, parents,children from syscat.tables where
tabname = 'DEPA
RTMENT' or TABNAME = 'EMPLOYEE'
TABNAME PARENTS CHILDREN
----------------------------------------------------
DEPARTMENT 0 1
EMPLOYEE 1 0
2 record(s) selected.
Thanks,
Sam.
Serge Rielau - 29 May 2006 16:43 GMT
syscat.references
TABSCHEMA/TABNAME shows children
REFTABSCHEMA/REFTABNAME shows parents

Signature
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mark A - 29 May 2006 16:45 GMT
> Is it possible to determine the list of child/parent tables for a
> particular table from any system catalog tables? Using the
[quoted text clipped - 18 lines]
> Thanks,
> Sam.
It is in there somewhere. Try looking at the constraints.
esmith2112@gmail.com - 31 May 2006 20:46 GMT
> Is it possible to determine the list of child/parent tables for a
> particular table from any system catalog tables? Using the
> syscat.tables I'm able to retrieve the no of dependent parent/child
> tables but unable to determine the list of dependent table name(s)
This all depends on depends on the declared foreign key constraints. If
you have defined the relationships, then this is possible. Serge
pointed out that these can be found in the SYSCAT.REFERENCES table. In
order to traverse the tree structure, a recursive query can be used.
Assuming you have the same schema name for all of the tables, the
following query should give you a list of parent/child relationships
including how many levels deep the relationship between the top level
table and any subservient table:
WITH parentchild
(
parentname,
childname,
depth
) AS
(SELECT reftabname,
tabname,
1
FROM syscat.references
WHERE reftabname= 'DEPARTMENT'
UNION ALL
SELECT child.reftabname,
child.tabname,
depth+1
FROM parentchild parent,
syscat.references child
WHERE parent.childname = child.reftabname
)
SELECT DISTINCT parentname,
childname,
depth
FROM parentchild
ORDER BY depth
Hope this helps,
Evan