Solved

Table Relationship Information in DB2-IBM for Windows

Posted on 2004-08-03
7
1,698 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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 Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now