Hi
In sysibm.sysrels we can see the relations between the table. Relation is in the form of table and refernece table. Table is the child and reference table as parent.
Now child itself can be parent to some other tables, so child becomes the refernce table and some other table as child. This way a tree kind of structure is defined.
My question is, how in a SQL query i can find the full tree structure ? Or is there any other of finding it.
Here is my fancy query which I coded while testing recursive SQL :
it uses three parameters : &tbcreator, &tbname & &lvls (& for use in Qmf )
lvls = how deep you want to go (and to avoid circular relations)
Code:
with
DOWN(lvl, reftbcreator, reftbname, creator, tbname, deleterule, enforced, relname, pad) as (
select cast (0 as integer) as lvl
, cast (' ' as varchar(128)) as reftbcreator
, cast (' ' as varchar(128)) as reftbname
, cast (creator as varchar(128)) as creator
, cast (name as varchar(128)) as tbname
, cast (' ' as char(1)) as deleterule
, cast (' ' as char(1)) as enforced
, cast (' ' as varchar(128)) as relname
, cast ( name as varchar(3000)) as pad
from sysibm.systables
where creator = &tbcreator and name = &tbname
union all
select H.lvl + 1
, R2.reftbcreator
, r2.reftbname
, r2.creator
, r2.tbname
, r2.deleterule
, r2.enforced
, r2.relname
, h.pad !!'-('!!r2.deleterule!! case when r2.enforced = 'N' then '*' else '' end !!')->'!! r2.tbname
from down H
join sysibm.sysrels R2 on R2.reftbcreator = H.creator and r2.reftbname = H.tbname and lvl < &lvls
where h.relname <> r2.relname and h.reftbname <> h.tbname )
,
up(lvl, reftbcreator, reftbname,creator, tbname, deleterule, enforced, relname, pad) as (
select 0 as lvl
, cast (creator as varchar(128)) as reftbcreator
, cast (name as varchar(128)) as reftbname
, cast (' ' as varchar(128)) as creator
, cast (' ' as varchar(128)) as tbname
, cast (' ' as char(1)) as deleterule
, cast (' ' as char(1)) as enforced
, cast (' ' as varchar(128)) as relname
, cast (strip(name) as varchar(3000)) as pad
from sysibm.systables
where creator = &tbcreator and name = &tbname
union all
select H.lvl - 1
, R2.reftbcreator
, r2.reftbname
, r2.creator
, r2.tbname
, r2.deleterule
, r2.enforced
, r2.relname
, strip(r2.reftbname) !!'-('!!r2.deleterule!!')->'!! strip(h.pad)
from sysibm.sysrels R2, up H
where h.reftbcreator = r2.creator and h.reftbname = r2.tbname
and h.relname <> r2.relname and h.reftbname <> h.tbname
and h.lvl > (-1 * &lvls)
my sincere apologies, i posted it in wrong forum unintentionally.
I used the following query, got some what the desired output.
Code:
WITH RPL ( level, REFTBNAME , TBNAME, RELNAME) AS
( SELECT 1 , ROOT.REFTBNAME ,
ROOT.TBNAME ,
ROOT.RELNAME
FROM SYSIBM.SYSRELS ROOT
WHERE ROOT.REFTBNAME = 'TGOVTRUSTPROP'
AND ROOT.CREATOR = 'CA'
UNION ALL
SELECT parent.level+1 ,CHILD.REFTBNAME , CHILD.TBNAME,
CHILD.RELNAME FROM RPL PARENT, SYSIBM.SYSRELS CHILD
WHERE PARENT.TBNAME = CHILD.REFTBNAME
AND child.CREATOR = 'CA'
AND PARENT.LEVEL < 3)
SELECT REFTBNAME ,LEVEL, TBNAME, RELNAME
FROM RPL
with ur ;