Solved

Table Relationship Information in DB2-IBM for Windows

Posted on 2004-08-03
7
1,721 Views
Last Modified: 2008-01-16
hi
i want to find the fields and table in DB2-IBM for Windows which contains information about the relationship, i.e Table Type, table name, pk and foreign key information.
In SQL server i can get it from this query
"select cc.table_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc where cc.constraint_name = rc.Unique_constraint_name ) as pk_table_name"
and
"select cc.column_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc where cc.constraint_name = rc.Unique_constraint_name) as pk_column_name"
but how can i get this information in DB2-IBM for Windows??
waiting for the reply
thanx
0
Comment
Question by:lightin_star
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 3

Expert Comment

by:lwilkin7
ID: 11716122
This will get you the relationships (constraints)

select reftbname as parenttbl
,tbname as childtbl
,relname as constraintnm
from sysibm.sysrels

This will get you the columns defined for the relationships

select tbname as fktbl
,relname as constraintnm
,colname as fkcolumn
,colseq as fkorder
from sysibm.sysforeignkeys
order by tbname,relname,colseq

This will get you the primary key columns

select tbname
,name as colname
,keyseq as pkorder
from sysibm.syscolumns
where keyseq > 0
order by tbname,keyseq

Of course you can combine these into one query that will give you all info by joining the tables together.  If you need help with that, let me know.

Lloyd
0
 

Author Comment

by:lightin_star
ID: 11723494
Thanx for the reply Lloyd (lwilkin7)
i have tried those queries but the second one ie
( select tbname as fktbl, relname as constraintnm, colname as fkcolumn, colseq as fkorder from sysibm.sysforeignkeys order by tbname,relname,colseq )
has an error... the description of the error is.....

DBA2191E SQL execution error.
com.ibm.db.DataException: A
database manager error occurred. : [IBM][CLI Driver][DB2/NT]
SQL0204N  "SYSIBM.SYSFOREIGNKEYS" is an undefined name.  
SQLSTATE=42704

can u please fix it?? and please tell me from where i will get the information that the datastore type is a Table or view or synonym or a system table????
i'll be waiting
Regards
0
 
LVL 13

Expert Comment

by:ghp7000
ID: 11725631
referentail constraints:
SELECT
            B.CONSTNAME AS "CONSTRAINT NAME",
            SUBSTR(RTRIM(B.REFTABSCHEMA)||'.'||B.REFTABNAME,1,30) AS "PARENT TABLE",
            SUBSTR(B.PK_COLNAMES,1,14) AS "PK COLUMN",
            SUBSTR(RTRIM(B.TABSCHEMA)||'.'||B.TABNAME,1,30) AS "FK TABLE NAME",
            SUBSTR(B.FK_COLNAMES,1,14) AS "FK COLUMN",
                  CASE B.DELETERULE
                        WHEN 'N' THEN 'SET NULL'
                        WHEN 'C' THEN 'CASCADE'
                        WHEN 'R' THEN 'RESTRICT'
                        WHEN 'A' THEN 'NO ACTION'
                  END AS "DELETE RULE",
            CASE B.UPDATERULE
                        WHEN 'A' THEN 'NO ACTION'
                        WHEN 'R' THEN 'RESTRICT'
                  END AS "UPDATE RULE",
            (CASE (SUBSTR(A.CONST_CHECKED,1,1))
                        WHEN 'Y' THEN 'CHECKED BY SYSTEM'
                        WHEN 'U' THEN 'CHECKED BY USER'
                        WHEN 'N' THEN 'CHECK PENDING'
                        WHEN 'W' THEN 'USER CHECK PENDING'
                  END) AS "CHECK STATUS"
FROM            SYSCAT.REFERENCES B, SYSCAT.TABLES A
WHERE             B.REFTABSCHEMA=A.TABSCHEMA
AND             B.REFTABNAME=A.TABNAME
AND             B.TABSCHEMA='TITAN'
ORDER BY      "CONSTRAINT NAME";        


indexes:
SELECT
      SUBSTR(RTRIM(A.INDSCHEMA)||'.'||A.INDNAME,1,30) AS "SCHEMA INDEX NAME",
      SUBSTR(RTRIM(A.TABSCHEMA)||'.'||A.TABNAME,1,24) AS "SCHEMA TABLE NAME",
      SUBSTR(RTRIM(A.COLNAMES),1,40) AS "COLUMN NAMES",
      CASE A.UNIQUERULE
            WHEN 'D' THEN 'DUPLICATES ALLOWED'
            WHEN 'P' THEN 'PRIMARY INDEX'
            WHEN 'U' THEN 'UNIQUE ONLY'
            END AS RULE,
      CASE A.INDEXTYPE
            WHEN 'CLUS' THEN 'CLUSTER'
            WHEN 'REG'  THEN 'REGULAR'
            END AS TYPE,
      SUBSTR(B.TBSPACE,1,15) AS TBSPACE,
      SUBSTR(B.INDEX_TBSPACE,1,15) AS "INDEX TBSPACE",
      A.STATS_TIME AS "LAST STATS TIME"
FROM       SYSCAT.INDEXES A, SYSCAT.TABLES B
WHERE       A.TABNAME=B.TABNAME
AND      A.TABSCHEMA='TITAN'
ORDER       BY  A.TABNAME, A.UNIQUERULE;





0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 13

Expert Comment

by:ghp7000
ID: 11725653
copy and paste each statement into a seperate file and execute with
db2 -tvf <file_name>
0
 

Author Comment

by:lightin_star
ID: 11750373
Thanx ghp7000
i have used ur queries... the first one is really informative... but the problem is im still not getting the information about the "datastore type" (i.e. whether it is Table, view, synonym or system table....)
if u have any solution for it please let me know
i'll be waiting
thanx again for the solution
Regards
0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 50 total points
ID: 11752079
ADD THIS COLUMN:
referentail constraints:
SELECT
          B.CONSTNAME AS "CONSTRAINT NAME",


         A.TYPE AS "TABLE TYPE",

          SUBSTR(RTRIM(B.REFTABSCHEMA)||'.'||B.REFTABNAME,1,30) AS "PARENT TABLE",
          SUBSTR(B.PK_COLNAMES,1,14) AS "PK COLUMN",
          SUBSTR(RTRIM(B.TABSCHEMA)||'.'||B.TABNAME,1,30) AS "FK TABLE NAME",
          SUBSTR(B.FK_COLNAMES,1,14) AS "FK COLUMN",
               CASE B.DELETERULE
                    WHEN 'N' THEN 'SET NULL'
                    WHEN 'C' THEN 'CASCADE'
                    WHEN 'R' THEN 'RESTRICT'
                    WHEN 'A' THEN 'NO ACTION'
               END AS "DELETE RULE",
          CASE B.UPDATERULE
                    WHEN 'A' THEN 'NO ACTION'
                    WHEN 'R' THEN 'RESTRICT'
               END AS "UPDATE RULE",
          (CASE (SUBSTR(A.CONST_CHECKED,1,1))
                    WHEN 'Y' THEN 'CHECKED BY SYSTEM'
                    WHEN 'U' THEN 'CHECKED BY USER'
                    WHEN 'N' THEN 'CHECK PENDING'
                    WHEN 'W' THEN 'USER CHECK PENDING'
               END) AS "CHECK STATUS"
FROM          SYSCAT.REFERENCES B, SYSCAT.TABLES A
WHERE           B.REFTABSCHEMA=A.TABSCHEMA
AND           B.REFTABNAME=A.TABNAME
AND           B.TABSCHEMA='<SCHEMANAME>'
ORDER BY     "CONSTRAINT NAME";        

0
 

Author Comment

by:lightin_star
ID: 11761680
Thanx ghp7000
im really greateful to u for ur help...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
Suggested Courses

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question