 Re: How to list tables in order (parent & child relationship)
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.references, leaf_children
where tabschema = 'DB2ADMIN'
and tabname = leaf_child)
union all
(select reftabschema, reftabname, tabschema, tabname, levels - 1
from refs r, syscat.references 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_child,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" <maricel@xtra.co.nz> wrote in message news:<5aITb.32508$9k7.683461@news.xtra.co.nz>...[color=blue]
> 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,third...
> 2) A list that shows which table should loaded first, second,third...
>
> Your help would be highly appreciated.
>
> maricel.[/color]
| 
