Finding the relation bw two tables that are related in DB2

amrita.chatterjee · Posted: Tue Sep 11, 2007 6:17 pm

Hi,

If the two tables are related in DB2(Say Table T000 & T001 and Table T000 is the parent table & T001 is the child table) so could you please tell me where I can find the following:
1.relation between these two tables
2.which column is common between two tables

Actually I'm not able to find out the relation in DDL.

Could you please help me in this regards?

dick scherrer · Posted: Tue Sep 11, 2007 7:25 pm

Hello,

If you look at the data columns and index definitions for the 2 tables, the relationship should be apparent.

It may be that the relationship is not obvious (it should be, but sometimes data structures are not as well defined as they could be). Look for a column in the "parent" that has a similar definition in the "child" that is an index (or part of an index) in the child. Again, it is usually the primary key, but it does not have to be.

amrita.chatterjee · Posted: Wed Sep 12, 2007 9:54 am

Actually I have checked the DDL of the two tables and also whatever index has been defined on the tables but no such primary key, FOREIGN KEY or REFERENCES are present. Is there any other way to find the relation?

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Look at this Source

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/E.8?DT=20010718164132

to find answer for ur query.

dick scherrer · Posted: Thu Sep 13, 2007 12:17 am

Hello,

If the tables have no common column(s) that are keyed (at least in the child), why do you consider them related?

Consider - If one of the tables is about employees and the other about mechanical components, they are not likely related at all. . .

If you describe what kind of data each table contains, that may help.

TG Murphy · Posted: Thu Sep 13, 2007 12:50 am

If the DBAs are using referential integrity you could query the catalog to fish this information out. However, it is not a foolproof approach.

You could query the SYSIBM.SYSCOLUMNS to which column names are used in more than one table. Again - not foolproof.

What we do here is this: We go to the DBAs and ask to see the data model. Nice boxes that represent tables - lines drawn from box to box that indicate 1-1 relationships / 1-to-many / many-to-many, etc. This is the proper way to get an understanding of your database.

dick scherrer · Posted: Thu Sep 13, 2007 12:55 am

Or none-to-none

Gotta love Entity Relationship Diagrams

amrita.chatterjee · Posted: Thu Sep 13, 2007 10:12 am

Hi,

Thanks for all the reply. I got lot of information from this.
Actually i think I can better describe the situation by the following example.

Table T000 >> has two column with name TBL_ID & TBL_PTR

Say value of these two columns are
TBL_ID TBL_PTR
T001 001
T024 012 like this.

Now T001, T024, these are again some tables.

So in those tables also same pointer colume is present and also other columns are present. So Just to take the correct entry from T001, I have to check the pointer in T000 & then with that pointer I have to go to the T001 & fetch the exact row.

For this reason only, I'm thinking that, these T000, T001 should be related.

For this reason, I have already checked the index of these two tables & DDL of these two Tables but I'm not able to find any relation.

Then I have run the following query:

SELECT TBCREATOR, TBNAME, NAME, KEYSEQ
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'XXXXXX'
AND TBNAME = 'T000'
AND KEYSEQ > 0
ORDER BY KEYSEQ;

But after running this query, I can found out that the value of KEYSEQ of every column is 0.

I'm not able to really understand how the relation has been established.

Thanks & Regards

Amrita.

dick scherrer · Posted: Thu Sep 13, 2007 1:15 pm

Hello,

From what you have posted so far, i see no true relationship, but i could be misunderstanding. If you explain differently, that may help.

dick scherrer · Posted: Sat Sep 15, 2007 12:20 am

Hello amrita.chatterjee,

Have you found what you needed?