Results 1 to 5 of 5

Thread: Cool Tip

  1. #1
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Smile Unanswered: Cool Tip

    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
    Last edited by ARWinner; 04-29-04 at 17:34.

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    Are Some lines missing?
    mota

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    No. I just ran in on our DB and it worked fine.

    Andy

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I should add that this works on V 8.1 UDB for LUW.

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

    Andy

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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