How to find out the relationship between two tables.

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

How to find out the relationship between two tables. I tried by SYSIBM.SYSRELS, but there is only relation name,could not find any common column for two tables.

Could any one suggest me how to proceed?

thank you,
Mahi

chettiyar · Posted: Fri Dec 01, 2006 6:54 pm

SYSIBM.SYSCOLUMNS identifies columns of a parent key in column KEYSEQ; a nonzero value indicates the place of a column in the parent key.

Code:
SELECT TBCREATOR, TBNAME, NAME, KEYSEQ
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'creater id'
AND TBNAME = 'table-name'
AND KEYSEQ > 0
ORDER BY KEYSEQ;

To find out the foreign key of a DB2 table from SYSIBM.

Code:
SELECT TBNAME, COLNAME, COLNO, CREATOR
FROM SYSIBM.SYSFOREIGNKEYS
WHERE TBNAME = 'table-name';

MFRASHEED · Active User Joined: 14 Jun 2005 Posts: 186 Location: USA

One more way to do the same is by PLATINUM utility:

From main menu select (1 RC/Query ), next screen enter table name in item name and select (RI - Ref.Integrity). Takes you of this menu and rest is self explanatory.