Have you ever wanted to get a listing of your tables that are sorted by how they are related to each other. The following SQL will give you that list with the tables on top having no references to other tables then tables referencing the ones already listed and so on for all the tables. This is very useful for several reasons. For example, after loading data into numerous tables using lLOAD, most of the tables will be in Check-pending state. They need to have SET INTEGRITY executed on them in the order that this SQL produces.
DO NOT RUN THIS SQL IF YOU HAVE CIRCULAR REFERENCES. It is recursive and will hang if there are circular references. It will however ignore self-referenced tables.
Here it is:
with temp1 as (
select distinct t.tabname,nullif(r.reftabname,t.tabname) as reftable from syscat.tables as t left outer join syscat.references as r on t.tabschema = r.tabschema and t.tabname = r.tabname
where t.tabschema = 'MySchema' ), <-- this will limit it to one schema
temp2 (table, reftable,lvl) as (
select tabname,reftable,1 from temp1 where reftable is null and tabname not in (select tabname from temp1 where reftable is not null)
select t.tabname,t.reftable,z.lvl+1 from temp1 as t, temp2 as z where t.reftable = z.table
) select table,max(lvl) as mlevel from temp2 group by table order by mlevel,table