IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SYSIBM.SYSRELS


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Fri Sep 04, 2009 3:26 pm
Reply with quote

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.

thanks
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Sep 04, 2009 3:31 pm
Reply with quote

I think this thread should be in DB2 forum
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Sep 04, 2009 3:51 pm
Reply with quote

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)

)


select * from up
union all
select * from down

order by lvl
Back to top
View user's profile Send private message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Fri Sep 04, 2009 5:57 pm
Reply with quote

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 ;
Code'd
Back to top
View user's profile Send private message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Mon Sep 28, 2009 7:39 pm
Reply with quote

Hi
I am trying to insert the resultant set of the above query into a table, but I am getting SQL -199.



I am using the below query. For the select I am getting the correct result of the recurisve query.

WITH RPL ( level, REFTBNAME , TBNAME, RELNAME) AS
( SELECT 1 , ROOT.REFTBNAME ,
ROOT.TBNAME ,
ROOT.RELNAME
FROM SYSIBM.SYSRELS ROOT
WHERE ROOT.REFTBNAME in ( 'TGOVTRUSTPROP' ,
'TGOVTRSERFEEPER' ,
'TGOVTAXINFORMATION' ,
'TGOVSTOPPINGLIST' ,
'TGOVPORTFOLIOSTRAT' ,
'TGOVINSTRRESTR' ,
'TGOVINSTITUTIONROL' ,
'TGOVCFDEFINITION' ,
'TGOVINSTRSERVICE' ,
'TGOVINSTITUTION' ,
'TGOVINSTRUMENT' ,
'TGOVCFPLAN' )
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 < 10)

SELECT REFTBNAME ,LEVEL, 'I', TBNAME, RELNAME
FROM RPL A

INSERT INTO D1.TJAEREFERENCE
SELECT REFTBNAME ,LEVEL, 'I', TBNAME, RELNAME
FROM A ;;;


For the INSERT in last I am getting -199, which says

SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD INSERT. TOKEN FOR
WITH FETCH ORDER UNION EXCEPT QUERYNO OPTIMIZE WAS EXPECTED


The sytax for the INSERT using SELECT is

INSERT into Table A
Select COL from Table B ;

Can this be the case that for RPL which is created on fly insert cannot be used.

thanks in advance.
Back to top
View user's profile Send private message
tomehta

New User


Joined: 18 Aug 2008
Posts: 98
Location: India

PostPosted: Mon Sep 28, 2009 9:44 pm
Reply with quote

Hi
I was able to resolve it. INSERT was to be given at the top along with WITH

insert into D1.TJAEREFERENCE
WITH RPL ( level, REFTBNAME , TBNAME,


nevertheless , thanks
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Discrepancy b/w SYSIBM tables and BMC... DB2 0
No new posts SYSIBM Tables Query DB2 8
No new posts Column names in SYSIBM tables DB2 5
No new posts Create view statement from SYSIBM.SYS... DB2 7
No new posts SYSIBM Catlog tables to see DDL chang... DB2 2
Search our Forums:

Back to Top