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.

syscat.references
TABSCHEMA/TABNAME shows children
REFTABSCHEMA/REFTABNAME shows parents


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

<reachsamdurai@gmail.com> wrote in message
news:1148907579.501908.282360@38g2000cwa.googlegro ups.com...
> 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.
>

It is in there somewhere. Try looking at the constraints.

reachsamdurai@gmail.com wrote:
> 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