• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1811
  • Last Modified:

Table Relationship Information in DB2-IBM for Windows

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
lightin_star
Asked:
lightin_star
  • 3
  • 3
1 Solution
 
lwilkin7Commented:
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
 
lightin_starAuthor Commented:
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
 
ghp7000Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
ghp7000Commented:
copy and paste each statement into a seperate file and execute with
db2 -tvf <file_name>
0
 
lightin_starAuthor Commented:
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
 
ghp7000Commented:
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
 
lightin_starAuthor Commented:
Thanx ghp7000
im really greateful to u for ur help...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now