Solved

Table Relationship Information in DB2-IBM for Windows

Posted on 2004-08-03
7
1,686 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Expert Comment

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

Author Comment

by:lightin_star
Comment Utility
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
Comment Utility
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
Comment Utility
Thanx ghp7000
im really greateful to u for ur help...
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

772 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

10 Experts available now in Live!

Get 1:1 Help Now