Solved

Table Relationship Information in DB2-IBM for Windows

Posted on 2004-08-03
7
1,729 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 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