Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / May 2006

Tip: Looking for answers? Try searching our database.

Parent Child Relationship - System catalog tables

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
reachsamdurai@gmail.com - 29 May 2006 13:59 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)

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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.