Try this:
with leaf_children (leaf_child) as
(select tabname from syscat.tables where tabschema = 'DB2ADMIN'
and parents > 0 and children = 0 ),
refs (parentschema, parent, childschema, child, levels) as
((
select reftabschema, reftabname, tabschema, tabname, 4
from syscat.referenc es, leaf_children
where tabschema = 'DB2ADMIN'
and tabname = leaf_child)
union all
(select reftabschema, reftabname, tabschema, tabname, levels - 1
from refs r, syscat.referenc es s
where s.tabschema = r.parentschema
and s.tabname = r.parent
and r.levels > 0))
select distinct substr(r.parent ,1,20) as parent, '>>> ' ||
substr(r.child, 1,20) as child, levels
from refs r, (select parent, min(levels) as level
from refs group by parent) as p
where r.parent = p.parent and r.levels = p.level
union
select substr(leaf_chi ld,1,20), ' ', (select max(levels) from refs) +
1 from leaf_children
order by 3;
This recurses down the RI chain and gives an ordered list of parents
pointing to children. You need to set the number of levels you expect
(start with a pessimistically high number) by modifying the last
number on this line, above:
select reftabschema, reftabname, tabschema, tabname, 4
You'll also want to change the schema name in the query.
Hope that helps
Sean
"maricel" <ma*****@xtra.c o.nz> wrote in message news:<5a******* *************@n ews.xtra.co.nz> ...
Is there a way to list (using db2 command or catalogs) to list hierarchy of
table parent & child relationship:
1) A list that shows which table should be deleted first,second,th ird...
2) A list that shows which table should loaded first, second,third...
Your help would be highly appreciated.
maricel.
