Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: Sort table names by foreign key dependencies in a schema/Database

    I want to generate a list of table names into a file in the order of FK dependencies. The purpose is to truncate all tables in a given database/schema by using the generated file in a "for loop". I use "Load from /dev/null..." or "Import.." command to truncate the tables. The database is IBM DB2 V 9 on an AIX platform. The below query gave me a list of tables that aren't parents, in other words list of Children that aren't parents. I need help in taking this further up the hierarchy. Thanks in advance for the help.

    db2 "WITH tmp
    (
    tabname
    ) AS
    (SELECT DISTINCT rtrim(reftabschema) || '.' || SUBSTR(reftabname,1,50)
    FROM syscat.references
    ORDER BY 1
    )
    SELECT distinct rtrim(tabschema) || '.' || SUBSTR(tabname,1,50)
    FROM syscat.references
    WHERE rtrim(tabschema) || '.' || SUBSTR(tabname,1,50)
    NOT IN (select * FROM tmp)"|awk '{print $1}'

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I posted this somewhere before, but I could not find it.

    Code:
    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' and t.type = 'T' )  , 
    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 as t 
    inner join syscat.tables as w on (t.table = w.tabname and w.tabschema = 'MySchema') 
    group by table order by mlevel,table
    Be careful with this since it is recursive and if you have a circular referencing scheme, it will never return. It does handle if a table references itself though.

    Andy

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    Thanks very much Andy, It worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •