-- Here are three queries against the system catalog to display family trees of parent and child tables.
-- This was written as an aid for development and test staff running up against integrity problems, particularly on IMPORTs and LOADs.
-- Supply your own schema and table names where appropriate. 
-- Note that most Java code operates against just a single, unqualified schema name these days. 
-- You will need to modify the join and predicate conditions if referential integrity crosses schema boundaries
-- (if it is enforced in Data Warehouse or Business Intelligence environments, it jolly well should cross boundaries)

-- Alex Levy t/a http://www.sustainablesoftware.net, June 2007.



-- Simple function used in remaining queries;

create function sub30( arg1 varchar(255))
returns char(30)
specific sub30 language sql
deterministic no external action
contains sql
return substr(arg1,1,30)
;
comment on function sub30 is 'Returns the first 30 characters of the string argument; particularly useful for shortening table names in admin query outputs.'
;


--[1] Display ALL parent-child relationships, in parent child order. 
-- For example:
--LEVYA.EXPLAIN_INSTANCE
--LEVYA.EXPLAIN_INSTANCE-->LEVYA.EXPLAIN_STATEMENT 
--LEVYA.EXPLAIN_INSTANCE-->LEVYA.EXPLAIN_STATEMENT-->LEVYA.EXPLAIN_ARGUMENT
--LEVYA.EXPLAIN_INSTANCE-->LEVYA.EXPLAIN_STATEMENT-->LEVYA.EXPLAIN_DIAGNOSTIC
--LEVYA.EXPLAIN_INSTANCE-->LEVYA.EXPLAIN_STATEMENT-->LEVYA.EXPLAIN_DIAGNOSTIC-->LEVYA.EXPLAIN_DIAGNOSTIC_DATA   

-- Start by selecting all tables that are NOT children i.e. that do not appear in the syscat.references tabname column;
-- then get their children and grandchildren etc. recursively.
-- Estimate the maximum  ancestry depth generously as double your actual maximum - in the code below max depth is 8. 
-- Allow 40 bytes per qualified table name, so 8 * 40 = 320 character display string.
-- The sub30 function below simply returns substr(colname,1,30)

with root (parent,depth,reftabschema,reftabname) 
  as ((select varchar(rtrim(reftabschema)||'.'||sub30(reftabname),320),0,reftabschema,reftabname
      from   syscat.references 
      where  reftabschema = 'LEVYA' 
             except
      select rtrim(tabschema)||'.'||sub30(tabname),0,tabschema,tabname
      from   syscat.references
      where  tabschema = reftabschema 
      and    tabname = reftabname) 
             UNION ALL
      select varchar(rtrim(root.parent)||'-->'||rtrim(tabschema)||'.'||sub30(r.tabname),320),depth+1,tabschema,tabname 
      from   root,syscat.references r 
      where  root.reftabname = r.reftabname
      and    root.reftabschema=r.reftabschema
      )
select trim(both ' ' from parent) as ancestry
from   root
where  depth < 9 
order by 1,depth asc
;


--[2] Show the ancestry line for ENTIRE parent-child chains, eliminating chains that are subsets of other chains. 
-- Stand-alone tables (i.e those without children, which are not themselves children) will appear at a depth of zero.
-- For example:
--LEVYA.EXPLAIN_INSTANCE-->LEVYA.EXPLAIN_STATEMENT-->LEVYA.EXPLAIN_ARGUMENT
--LEVYA.EXPLAIN_INSTANCE-->LEVYA.EXPLAIN_STATEMENT-->LEVYA.EXPLAIN_DIAGNOSTIC
--LEVYA.EXPLAIN_INSTANCE-->LEVYA.EXPLAIN_STATEMENT-->LEVYA.EXPLAIN_DIAGNOSTIC-->LEVYA.EXPLAIN_DIAGNOSTIC_DATA   
--   ... will all display, but:
--LEVYA.EXPLAIN_INSTANCE-->LEVYA.EXPLAIN_STATEMENT    
--LEVYA.EXPLAIN_INSTANCE-->LEVYA.EXPLAIN_STATEMENT-->LEVYA.EXPLAIN_DIAGNOSTIC
--  ... will not, because they are contained in other chains, shown at [1] 
-- The method used below - (recursive select followed by cartesian join) is grossly inefficient but acceptable at small volumes of data. 

set current schema LEVYA;

with root (parent,depth,reftabname) as
     (select varchar(sub30(reftabname),240),0,reftabname
      from   syscat.references 
      where  tabschema = current schema
             EXCEPT
      select sub30(tabname),0,tabname
      from   syscat.references
      where  tabschema = current schema  
             UNION ALL
      select varchar(root.parent||'-->'||sub30(r.tabname),240),depth+1,tabname 
      from   root,syscat.references r 
      where  root.reftabname = r.reftabname
      and    tabschema = current schema
     ),
     lines(parent1,parent2) as 
     (select r1.parent, r2.parent 
      from   root r1, root r2 
      where  r1.parent <> r2.parent
     )
select case
       when sum(locate(parent1,parent2)) = 0 
       then parent1
       end
from   lines
group by parent1 having sum(locate(parent1,parent2)) = 0
order by parent1
;


--[3]  Finds constraint and table/column names for the IMMEDIATE parents or children of any single given table.

select db2iqa1.sub30(constname) as child_fk_constname, 
       db2iqa1.sub30(tabname) as childtab, 
       db2iqa1.sub30(refkeyname) as parent_refkeyname,
       db2iqa1.sub30(reftabname) as parenttab,
       colcount,
       updaterule,
       deleterule,
       fk_colnames,
       pk_colnames 
from syscat.references
where tabschema = reftabschema
and   tabschema = 'MYSCHEMA' 
and   
(     tabname = 'XYZ'
or    reftabname = 'XYZ'  
);


 

