Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SYSIBM.SYSRELS

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: SYSIBM.SYSRELS
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    Post subject:
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    Post subject:
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    Post subject: Reply to: SYSIBM.SYSRELS
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    Post subject: Reply to: SYSIBM.SYSRELS
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    Post subject: Reply to: SYSIBM.SYSRELS
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts ERM-Diagram of SysIBM-Catalog-Tables Auryn DB2 2 Fri Feb 17, 2017 6:22 pm
No new posts Can not find SQL statement of package... chaoj DB2 2 Wed Mar 11, 2015 12:00 pm
No new posts -551 on SYSIBM.SYSTABLES, SYSIBM.SYST... baramesh DB2 5 Mon Dec 23, 2013 6:29 pm
No new posts SYSIBM.SYSPACKSTMT - DB2 V9 TO V10 gylbharat DB2 22 Fri Oct 25, 2013 3:36 pm
No new posts Copy Pending status flag in the SYSIB... pmvino DB2 1 Tue May 24, 2011 12:34 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us