Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to find Primary key of a table using query??

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

New User


Joined: 20 Jul 2006
Posts: 48
Location: Chennai

PostPosted: Tue Oct 30, 2007 2:27 pm    Post subject: How to find Primary key of a table using query??
Reply with quote

Hi,

Is it possible to find the primary key of a table by querying the SYSIBM tables? If so how?? I was asked this question in an interview. I wish to know whether this is possible and the solution also.

Thanks and Regards,
Sakthi.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10255
Location: italy

PostPosted: Tue Oct 30, 2007 2:31 pm    Post subject: Reply to: How to find Primary key of a table using query??
Reply with quote

search the forums the answer has been replied to many times
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Tue Oct 30, 2007 2:48 pm    Post subject: Reply to: How to find Primary key of a table using query??
Reply with quote

sakthi,

Check the following topics.

http://ibmmainframes.com/viewtopic.php?t=2829

http://ibmmainframes.com/viewtopic.php?t=262
Back to top
View user's profile Send private message
sakthi_ksv

New User


Joined: 20 Jul 2006
Posts: 48
Location: Chennai

PostPosted: Tue Oct 30, 2007 3:18 pm    Post subject:
Reply with quote

hi,

Through one of the forum given by Aaru I tried to find the PK using the following query

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


None of the columns of tables have KEYSEQ >0. Does that mean that all the tables wont have a PK. Also none of the tables have entries in SYSKEYS or SYSINDEXES tables. So does that mean that there is no PK or index in my application??

special thanks to those who helped me in searching the forum to find the solution.

Thanks and Regards,
Sakthi.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Oct 30, 2007 7:53 pm    Post subject:
Reply with quote

sakthi_ksv wrote:
hi,

Through one of the forum given by Aaru I tried to find the PK using the following query

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


None of the columns of tables have KEYSEQ >0. Does that mean that all the tables wont have a PK. Also none of the tables have entries in SYSKEYS or SYSINDEXES tables. So does that mean that there is no PK or index in my application??

special thanks to those who helped me in searching the forum to find the solution.

Thanks and Regards,
Sakthi.

Are you sure you are using the correct values for creator-id and table-name. Tables with no Primary Keys and no indexes sounds a little strange.
Back to top
View user's profile Send private message
sakthi_ksv

New User


Joined: 20 Jul 2006
Posts: 48
Location: Chennai

PostPosted: Wed Oct 31, 2007 11:33 am    Post subject:
Reply with quote

Ya, I am sure... I am using right values. Does that mean that there is no PK for the tables. If so if there is anyother means to know the PK??

Please let me know.

Thanks in advance,
Sakthivel.
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: Wed Oct 31, 2007 8:35 pm    Post subject:
Reply with quote

Hello,

It is not likely that there are no primary keys for multiple tables.

Try the query without naming the creator or the table name and see how many primary keys are identified. If none are found, it is almost surely a problem with the query.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Oct 31, 2007 9:53 pm    Post subject:
Reply with quote

Why wouldn't you be going against SYSINDEXES to find the name of the primary key on the table. Then finding the columns in that index?
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Thu Nov 01, 2007 9:43 am    Post subject:
Reply with quote

As Steve said, The following query on SYSIBM.SYSINDEXES can also be used for finding out the primary key.

SELECT TBCREATOR, TBNAME, NAME, CREATOR, DBNAME, INDEXSPACE FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR = ’creater id’ AND TBNAME = ’table name’ AND UNIQUERULE = ’P’;
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 find RACF group for access to spooled... jzhardy JCL & VSAM 1 Mon May 08, 2017 11:46 am
No new posts To find out size allocated to a seque... ashek15 JCL & VSAM 15 Thu Apr 27, 2017 9:42 am
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us