ok, so you are saying that
- there is no table without a parent.
- you don't know which table(s) to put first;
Let's see if there are tables without children (they are last in the load order) :
Code:
select name ,
(select count(*) from sysibm.sysrels R
where R.creator = tb.creator and R.tbname <> tb.name
and r.reftbcreator = tb.creator and r.reftbname = tb.name)
from sysibm.systables tb
where creator = 'xxx'
and type = 'T'
and not( criteria_for_historytables)
order by 2 asc
Otherwise you might want to :
- exclude not-enforced RI (probably all defined RI is enforced as in most shops)
- exclude the RI with DELETERULE = Set NULL
Code:
select name ,
(select count(*) from sysibm.sysrels R
where R.creator = tb.creator and R.tbname = tb.name
and r.reftbcreator = tb.creator and r.reftbname <> tb.name
and ENFORCED = 'Y'
and DELETERULE <> 'N')
from sysibm.systables tb
where creator = 'xxx'
and type = 'T'
and not( criteria_for_historytables)
order by 2 asc
I find it very discomforting that you have 333 tables and that there is a table with 584 children
ok, this is the reversed from my first query :
Select all tables without children ;
Select all parents,grandparents,... from these tables (recursive)
from all duplicate occurences of a table take the minimum lvl (= highest up in the tree)
Code:
with alltables(lvl,creator,name,list) as (
select 0 ,creator, name,cast(strip(name) as varchar(2000)) from sysibm.systables T
where type = 'T'
and creator = ?
and not( criteria_for_historytables)
and not exists (select * from sysibm.sysrels R
where R.reftbname = t.name and R.CREATOR = t.CREATOR
and R.tbname <> t.name and R.reftbCREATOR = t.CREATOR)
union all
select P.lvl - 1, r2.reftbcreator,r2.reftbname,strip(reftbname) || '>' || p.list
from alltables P, sysibm.sysrels R2
where r2.tbname = p.name and r2.creator = p.creator
and position(strip(r2.reftbname),p.list,CODEUNITS32 ) = 0
and lvl > -20
)
select
--*
creator, name, min(lvl)
from alltables
group by creator, name
order by 3,1,2
.jpg)
