Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Finding the relation bw two tables that are related in DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
amrita.chatterjee

New User


Joined: 27 Apr 2006
Posts: 48
Location: Bangalore, India

PostPosted: Tue Sep 11, 2007 6:17 pm    Post subject: Finding the relation bw two tables that are related in DB2
Reply with quote

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?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Sep 11, 2007 7:25 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
amrita.chatterjee

New User


Joined: 27 Apr 2006
Posts: 48
Location: Bangalore, India

PostPosted: Wed Sep 12, 2007 9:54 am    Post subject: Reply to: Finding the relation bw two tables that are relate
Reply with quote

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?
Back to top
View user's profile Send private message
mkk157

Active User


Joined: 17 May 2006
Posts: 310

PostPosted: Wed Sep 12, 2007 4:02 pm    Post subject: Hi Amrita
Reply with quote

Look at this Source

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

to find answer for ur query.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Sep 13, 2007 12:17 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Thu Sep 13, 2007 12:50 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Sep 13, 2007 12:55 am    Post subject:
Reply with quote

Or none-to-none icon_wink.gif

Gotta love Entity Relationship Diagrams icon_cool.gif
Back to top
View user's profile Send private message
amrita.chatterjee

New User


Joined: 27 Apr 2006
Posts: 48
Location: Bangalore, India

PostPosted: Thu Sep 13, 2007 10:12 am    Post subject: Reply to: Finding the relation bw two tables that are relate
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Sep 13, 2007 1:15 pm    Post subject:
Reply with quote

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.

Quote:
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.
What is it that you are looking for?

You might try
Code:
keyseq is not null
instead of
Quote:
KEYSEQ > 0

Quote:
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.
Please clarify what this is telling us. It sounds like when you have a ptr value you need to read the row with the same ptr value.

What i guess i'm saying is that i do not yet understand what info you are looking for icon_confused.gif

If we persist, we will get there icon_smile.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Sep 15, 2007 12:20 am    Post subject:
Reply with quote

Hello amrita.chatterjee,

Have you found what you needed?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts Question related to Data dictionary f... rexx77 SYNCSORT 3 Thu Aug 31, 2017 7:23 am
No new posts Finding IMS resource blocker after se... Hooman24 IMS DB/DC 1 Wed Aug 16, 2017 9:17 pm
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts finding delayed job/s in OPC jzhardy IBM Tools 0 Mon May 29, 2017 9:04 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us