Query, which find foreign keys and columns which referencing in DB2 z/OS

Please, can you somebody help me. I need to find a foreign keys and it's parent keys(primary keys) and its name.

For example, foreign_key1 references primary_key1

there is a query

SELECT A.CREATOR||'.'||A.TBNAME||'.'||B.COLNAME AS FOREIGN_KEY, A.RELNAME, A.REFTBNAME
FROM SYSIBM.SYSRELS A,
SYSIBM.SYSFOREIGNKEYS B
WHERE A.CREATOR = B.CREATOR
AND B.CREATOR in (SCHEMA)
AND A.TBNAME = B.TBNAME
AND A.RELNAME = B.RELNAME
ORDER BY A.RELNAME, B.COLSEQ

And i need also name of the primary key, please

Views: 555

Reply to This

Replies to This Discussion

Hello, 

Primary key exist via keyseq in sysibm.syscolumns 

with the following query you can perhaps find other informations   like colonne of the primary key and referenced columns in the children tables 

WITH BASE AS
(SELECT CREATOR,
NAME
FROM SYSIBM.SYSTABLES
WHERE DBNAME = ?),
KEY_PAR AS
(SELECT CO.TBCREATOR,
CO.TBNAME,
TRIM(L ','
FROM REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "C", CO.NAME)
ORDER BY CO.KEYSEQ)) AS VARCHAR(2000)),'<C>',','),'</C>','')) AS "COLUMNS"
FROM SYSIBM.SYSCOLUMNS CO
INNER JOIN BASE BA ON CO.TBCREATOR = BA.CREATOR
AND CO.TBNAME = BA.NAME
WHERE KEYSEQ > 0
GROUP BY CO.TBCREATOR,
CO.TBNAME),
KEY_CHI AS
(SELECT CO.CREATOR,
CO.tbNAME,
TRIM(L ','
FROM REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "C", CO.COLNAME)
ORDER BY CO.colSEQ)) AS VARCHAR(2000)),'<C>',','),'</C>','')) AS "COLUMNS"
FROM SYSIBM.SYSFOREIGNKEYS CO
INNER JOIN BASE BA ON CO.CREATOR = BA.CREATOR
AND CO.tbNAME = BA.NAME

GROUP BY CO.CREATOR,
CO.tbNAME)
SELECT TRIM(RE.REFTBCREATOR)||'.'||TRIM(RE.REFTBNAME) AS PARENT_TABLE,
KP.COLUMNS AS PARENT_COLUMNS,
TRIM(RE.CREATOR)||'.'||TRIM(RE.TBNAME) AS CHILDREN_TABLE,
Kc.COLUMNS AS Children_COLUMNS,
RELNAME,
CASE
WHEN DELETERULE = 'C' THEN 'Cascade'
WHEN DELETERULE = 'R' THEN 'Restrict'
WHEN DELETERULE = 'N' THEN 'Set Null'
ELSE 'No Action'
END AS DELETERULE
FROM SYSIBM.SYSRELS AS RE
INNER JOIN BASE AS BA ON RE.CREATOR = BA.CREATOR
AND RE.TBNAME = BA.NAME
INNER JOIN KEY_PAR AS KP ON KP.TBCREATOR = RE.REFTBCREATOR
AND KP.TBNAME = RE.REFTBNAME
INNER JOIN KEY_CHI AS KC ON KC.CREATOR = RE.CREATOR
AND KC.TBNAME = RE.TBNAME
ORDER BY REFTBCREATOR,
REFTBNAME

The columns that are part of the PK can be found in the SYSIBM.SYSCOLUMNS table (in the column KEYSEQ), the name of the primary key can be found in SYSIBM.SYSTABCONST (in the column  CONSTNAME)

The query can look like this :

SELECT SUBSTR(PK.CONSTNAME, 1, 18) AS PK_NAME,              
       SUBSTR(FK.RELNAME, 1, 18) AS FK_NAME,                
       CASE WHEN REL.DELETERULE = 'A' THEN 'NO ACTION'      
            WHEN REL.DELETERULE = 'C' THEN 'CASCADE'        
            WHEN REL.DELETERULE = 'N' THEN 'SET NULL'      
            WHEN REL.DELETERULE = 'R' THEN 'RESTRICT'      
            ELSE REL.DELETERULE                            
       END AS DELETERULE,                                  
       SUBSTR(FK.CREATOR, 1, 8) AS FK_CREATOR_TAB,          
       SUBSTR(REL.REFTBCREATOR, 1, 8) AS PAR_CREATOR_TAB,  
       SUBSTR(FK.TBNAME, 1, 18) AS FK_TABNAME,              
       SUBSTR(REL.REFTBNAME, 1, 18) AS PAR_TABNAME,        
       SUBSTR(FK.COLNAME, 1, 18) AS FK_COLNAME,            
       SUBSTR(COL.NAME, 1, 18) AS PK_COLNAME,              
       COLSEQ AS FK_COLSEQ                                  
  FROM SYSIBM.SYSFOREIGNKEYS FK,                            
       SYSIBM.SYSRELS REL,                                  
       SYSIBM.SYSCOLUMNS COL,                              
       SYSIBM.SYSTABCONST PK                                
  WHERE FK.CREATOR = 'QQ'                                  
    AND FK.RELNAME = REL.RELNAME                            
    AND REL.REFTBCREATOR = COL.TBCREATOR                    
    AND REL.REFTBNAME    = COL.TBNAME                      
    AND FK.COLSEQ = COL.KEYSEQ                              
    AND COL.TBCREATOR = PK.TBCREATOR                        
    AND COL.TBNAME    = PK.TBNAME                          
    AND PK.TYPE = 'P'                                      
--ORDER BY FOREIGN KEY NAME
 --ORDER BY 2, 10                                              
 --ORDER BY DEPENDENT TABLE, FOREIGN KEY NAME                  
 --ORDER BY 6, 4, 2, 10                                        
 -- ORDER BY PARENT TABLE, DEPENDENT TABLE, FOREIGN KEY NAME  
   ORDER BY 7, 5, 6, 4, 2, 10                                  
  ;                                                    

RI parent key does not have to be a primary key, it may be a unique key as well. Parent key = primary key assumption will cater for most (usual), but not all cases. E.g. you may notice it would miss some catalog RI.

If you'd like to build on your join solution, you could add SYSINDEXES and SYSKEYS in which may be more universal and work with both Primary and Unique constrains in parents. Get the name of either primary index (UNIQUERULE='P') or unique index supporting unique constraint in RI (UNIQUERULE='R') and matching corresponding SYSRELS columns. Then get columns of that IX. You may still find this missing some RI where for some reason indexes supporting primary keys are not marked as 'P'. I'm not sure why that's the case, possibly some historical reason or tables created a long time ago. There may even be some of it that on your catalog. So verify your results against plain SYSFOREIGNKEYS.

I've put together an example below with some comments. It should perform reasonably and get the results with a caveat as described above. But it's not something I use or tested, I've just put it together now as an example, so test it before you trust it :)

Also if you're looking for performance, more formatting/functionality and arguably better readability, check out Recursive CTEs. They are very nice and can be very quick. You can find great examples to get you started in IDUG archives e.g. "Recursive SQL – Unleash the Power!" by Suresh Sane.

<samp1>

SELECT F.RELNAME c_tab
, F.TBNAME c_tab
, F.COLNAME c_col
, F.COLSEQ c_col_seq
, R.REFTBNAME p_tab
, R.IXNAME p_index_unique
, I.name p_index_pk
, K.colname p_col

FROM SYSIBM.SYSFOREIGNKEYS F
, SYSIBM.SYSRELS R
, SYSIBM.SYSindexes I
, SYSIBM.SYSKEYS K

-- Pre-join filters
WHERE F.CREATOR = 'SYSIBM' -- <f> FILTER

-- join predicates
AND f.creator = r.creator -- <f>+<r> join
AND f.tbname = r.tbname
AND f.relname = r.relname

-- So which IX is supporting parent key?
AND ( i.tbname = r.reftbname -- <r>+<i> join
and i.creator = r.reftbcreator
and ( ( i.uniquerule = 'P' -- parent_key = pri
and R.IXNAME = '' -- cartesian warning
)
or ( i.uniquerule = 'R' -- parent_key = unique
and i.name = R.IXNAME
and i.creator = R.IXOWNER
)
)
)

-- You'll get your key cols from syskeys.
AND i.name = k.ixname -- <i>+<k> join
AND i.creator = k.ixcreator
-- Dont end up with a cartesian product on <i>+<k>
AND f.colseq = k.colseq

order by F.relname, F.COLSEQ
with ur;

</samp1>

* Sorry about code formatting. This WYSIWYG won't win any prizes for that ;)

Yes very good. Could include extra Stage 1 join predicate:

AND I.UNIQUERULE IN ('P','R')

I like as many Boolean term Stage 1 predicates as possible.

I am surprised that the original question did not ask for the Foreign Key Indexes or lack of support for Foreign Key by an Index. All my FK queries are more oriented to that. The index used for the parent key seems less important, although there certainly has to be one.

Michael H

 

RSS

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

User Groups

Blog Posts

New SQL pagination syntax - a Db2 12 greatest hit with John Campbell

Posted by Paul McWilliams on May 22, 2018 at 21:25 0 Comments

Db2 12 for z/OS introduces new SQL syntax that makes the difficult task of coding efficient SQL pagination—returning a portion of a result set based on data position—much easier for application developers. Read more in…

Continue

Videos

  • Add Videos
  • View All

© 2018   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service