IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Find out the primary key of the table provided with the name


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
tosaurabh20

New User


Joined: 08 Jun 2007
Posts: 26
Location: Noida

PostPosted: Mon Jul 16, 2007 1:38 pm
Reply with quote

Hi Al.

Can anyone tell me how to find out the primary key of the table provided with the name of that table??

Let me know in case further details are required.

Thanks
Saurabh
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India

PostPosted: Mon Jul 16, 2007 1:45 pm
Reply with quote

Saurabh,

Quote:
Can anyone tell me how to find out the primary key of the table provided with the name of that table??


Have you heard of sys tables?
Back to top
View user's profile Send private message
tosaurabh20

New User


Joined: 08 Jun 2007
Posts: 26
Location: Noida

PostPosted: Mon Jul 16, 2007 1:50 pm
Reply with quote

murmohk1 wrote:
Saurabh,

Quote:
Can anyone tell me how to find out the primary key of the table provided with the name of that table??


Have you heard of sys tables?


I have heard about them but do let me know where it will specifically show that this column is a primary key for a table?

Your quick response is appreciable.

Thanks
Saurabh
Back to top
View user's profile Send private message
ParagChouguley

Active User


Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)

PostPosted: Mon Jul 16, 2007 2:06 pm
Reply with quote

Hi tosaurabh20,

First go to table SYSIBM.SYSINDEXES and obtain index name and its creator for your table. If multiple indexes found, select the index which is restricted with unique value clause(means group of columns associated with that index will have unique value)
Then go to table SYSIBM.SYSKEYS and obtain columns associated to that INDEX and creator combination.

--Parag
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India

PostPosted: Mon Jul 16, 2007 2:31 pm
Reply with quote

ParagChouguley wrote:
table SYSIBM.SYSINDEXES and obtain index name

Hi,

Is it a valid table name, second qualifier (SYSINDEXES) contains 10-characters. I couldn't find this table in my system. Please suggest some other way.
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India

PostPosted: Mon Jul 16, 2007 2:46 pm
Reply with quote

Anuj,
Quote:
Is it a valid table name, second qualifier (SYSINDEXES) contains 10-characters

Yes, it is valid.
Back to top
View user's profile Send private message
kussu
Warnings : 1

New User


Joined: 16 May 2007
Posts: 33
Location: India

PostPosted: Mon Jul 16, 2007 4:44 pm
Reply with quote

Hi,

Are you using platinum tool for accessing DB2 tables???
If so, there you can easily find out the Primary key for the given table

The primary key columns are mentioned with different color ,
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Mon Jul 16, 2007 11:05 pm
Reply with quote

Or if you have ADBL and have permissions to it. We have to run it as TSO @ADBL, pick the DB2 system we want to access. Select 1, then T to get a list of tables (fill in selection criteria at the bottom if you want to restrict the returned tables). On the line with the table you want select with an X. Look for the row that has the column U filled in with P. Select that line with a C. You should see your primary key columns.
Back to top
View user's profile Send private message
pankajsoni

New User


Joined: 21 Jun 2006
Posts: 9
Location: Zurich

PostPosted: Tue Jul 17, 2007 3:59 pm
Reply with quote

Use the below query: (change the tabname & creator in the last line)

Code:
SELECT
 A.NAME,A.TBNAME,A.TBCREATOR,A.COLTYPE,
 A.LENGTH,A.COLNO,A.NULLS,
 CASE
  WHEN D.RELNAME = C.RELNAME
    THEN D.REFTBCREATOR!!'.'!!D.REFTBNAME!!'('!!E.NAME!!')'
  ELSE ''
 END AS REF,
 CASE
  WHEN A.NAME = B.COLNAME THEN 'P'
  WHEN A.NAME = C.COLNAME THEN 'F'
  ELSE ''
 END AS KEY
FROM SYSIBM.SYSCOLUMNS AS A
LEFT OUTER JOIN SYSIBM.SYSKEYCOLUSE AS B
  ON  A.NAME      = B.COLNAME
 AND  A.TBCREATOR = B.TBCREATOR
 AND  A.TBNAME    = B.TBNAME
LEFT OUTER JOIN SYSIBM.SYSFOREIGNKEYS AS C
  ON  C.CREATOR   = A.TBCREATOR
 AND  C.TBNAME    = A.TBNAME
 AND  C.COLNAME   = A.NAME
LEFT OUTER JOIN SYSIBM.SYSRELS AS D
  ON   D.CREATOR   = C.CREATOR
 AND  D.TBNAME    = C.TBNAME
 AND  D.RELNAME   = C.RELNAME
LEFT OUTER JOIN SYSIBM.SYSCOLUMNS AS E
  ON   E.TBNAME    = D.REFTBNAME
 AND  E.TBCREATOR = D.REFTBCREATOR
 AND  C.COLSEQ    = E.KEYSEQ
WHERE A.TBCREATOR='XXXX' AND A.TBNAME='XXXXXXXX'  ORDER BY A.COLNO
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India

PostPosted: Thu Jul 19, 2007 8:56 pm
Reply with quote

murmohk1 wrote:
Yes, it is valid.

Thanks for the clarification, but I couldn't get the that table-name in my system. Can you please suggest some other table-name for the same purpose.

Kussu,
No, we are not using platinum tool in our system.


stodolas,
What is ADBL? TSO @ADBL does not work for me. In my system I usually use TSO BMCADM to invoke DB2 related utilities.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Thu Jul 19, 2007 9:21 pm
Reply with quote

ADBL is the DB2 admin tool. You may not have permissions to run it. Check with your DBAs
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India

PostPosted: Thu Jul 26, 2007 11:34 am
Reply with quote

stodolas wrote:
Check with your DBAs

This is one drawback when you work from offshore, we don't have a direct access to our system DBAs, any alternate way to find whether these type of tools (ADBL) are installed on our site & me, as an end user, can use them.
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 177
Location: Seattle, WA

PostPosted: Sat Jul 28, 2007 12:13 am
Reply with quote

You could try emailing you DBAs.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Sat Jul 28, 2007 6:43 am
Reply with quote

pankajsoni: That is a very very convuluted query to get the primary key
for a table.

Try using the following. It should be much easier to follow.

Code:

SELECT SI.TBCREATOR, SI.TBNAME, SK.COLNAME, SK.COLSEQ 
FROM SYSIBM.SYSINDEXES SI, SYSIBM.SYSKEYS SK
WHERE SI.NAME = SK.IXNAME
  AND SK.IXCREATOR = SI.TBCREATOR
  AND SI.TBCREATOR = 'XXXX'
  AND SI.TBNAME = 'YYYY'
  AND SI.UNIQUERULE = 'P'
--Puts them in the order they are in the PK
ORDER BY COLSEQ
WITH UR;


Replace XXXX with the table creator and YYYY with the actual table name.

If you don't have XXXX you can leave that off and get all tables in that
DB2 subsystem with that name.

anuj_model: I can only find my DBA if I walk across the campus and hunt
them down and then hopefully I picked a good time so they aren't in a
meeting. Email them. Someone has to be maintaing your system and be able
to give you direction as to what tools you do and don't have availbable.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India

PostPosted: Sat Jul 28, 2007 12:00 pm
Reply with quote

socker_dad wrote:
You could try emailing you DBAs.
Yeah, this is one way, I'll try.

todolas wrote:
I walk across the campus and hunt them down and then hopefully I picked a good time so they aren't in a meeting.
I cann't just walk from India to US/ Florida icon_wink.gif (I guess, our DBAs work from there). You got enough advantages to hunt your DBAs in your own campus icon_smile.gif
todolas wrote:
Email them.
Yeah, I'll try this & will be back to you guys, if they ask something new.

Thanks for the suggestion, let me take my time.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin

PostPosted: Sat Jul 28, 2007 8:19 pm
Reply with quote

My DBA also has a reputation for not taking your calls except between 10 am and 2 pm as long as he isn't on lunch or break in there.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India

PostPosted: Mon Aug 06, 2007 4:34 pm
Reply with quote

Hi,

You've taken it otherwise man, I just wanted to say that I 'm located far enough from my DBAs' work location.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Aug 06, 2007 5:18 pm
Reply with quote

the appendixes in any DB2 SQL Application Programmers Guide has a list of all the SYSIBM tables, their construct, and examples of queries against them.


look in the manuals!!!!!!!!!!!!!!!!!!!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts sort to find out the char which repea... Mainframe Interview Questions 10
No new posts Compare 2 alphabets in a REXX and fin... CLIST & REXX 2
No new posts Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
No new posts Inserting into table while open selec... DB2 1
Search our Forums:

Back to Top