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)
union all
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


Andy

Are Some lines missing?

No. I just ran in on our DB and it worked fine.

Andy

I should add that this works on V 8.1 UDB for LUW.

(I do not know if will work for anything else)

Andy

It does work on Version 7 also (syntactically) ...

For those who want to use this , the text
<-- this will limit it to one schema

should be removed ...

Andy has given this line as a 'help' ... Remember, an SQL comment in a script '--' should always be at the beginning of the line




Cheers
sathyaram