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 can I find the Primary key of a table?

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

New User


Joined: 18 May 2003
Posts: 7
Location: Bangalore

PostPosted: Sun Feb 15, 2004 8:54 pm    Post subject: How can I find the Primary key of a table?
Reply with quote

Dear Guys,

Can anybody clear:

How to identify my primary key of the table, if I don't have permission to view SYSIBM.* Tables............
Back to top
View user's profile Send private message

mcmillan

Site Admin


Joined: 18 May 2003
Posts: 1203
Location: India

PostPosted: Mon Feb 16, 2004 5:56 pm    Post subject:
Reply with quote

You can easily find it from the system catalog.

Or try to display all the columns of the table. The primary key column don't have any duplicates.(if its not a complex key)

If you have any other columns as unique, try to insert a duplicate value or NULL for each columns one by one.

Using this tech. you can easily identify the primary key.
Back to top
View user's profile Send private message
dhan_chander

New User


Joined: 26 Feb 2004
Posts: 1
Location: Chennai

PostPosted: Thu Feb 26, 2004 11:27 am    Post subject: How To Identify a Primary Key in DB2
Reply with quote

Hi Mac,
I just tried selecting all the columns from the table, the display was in the order of insertion but not in ascending order. kindly make it clear.

Cheers
Viju icon_question.gif
Back to top
View user's profile Send private message
uvraman

New User


Joined: 05 Mar 2004
Posts: 2
Location: Bangalore

PostPosted: Fri Mar 05, 2004 4:53 pm    Post subject: Identifying Primary key
Reply with quote

Hi,

You can try running this SQL

SELECT * FROM SYSIBM.SYSKEYS
WHERE IXCREATOR='<creator name>'

This will give the column which is part of primary key along with the column number, colun sequence and ordering.

Hope this helps.
Back to top
View user's profile Send private message
Jaya kishan Y

New User


Joined: 04 Jan 2006
Posts: 1
Location: Trivandrum

PostPosted: Wed Jan 04, 2006 5:52 pm    Post subject:
Reply with quote

hai guys,

what is the exact reason for status code 4
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Thu Jan 05, 2006 3:46 am    Post subject:
Reply with quote

By using the "Platinum"
Back to top
View user's profile Send private message
winchaat

New User


Joined: 23 Mar 2005
Posts: 14

PostPosted: Thu Jan 05, 2006 11:54 am    Post subject: To find primary key
Reply with quote

SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'tablecreator' AND TBNAME = 'tbaname' AND KEYSEQ > 0;

hope this helps...



---->RAG
Back to top
View user's profile Send private message
sairam

New User


Joined: 30 Aug 2005
Posts: 58
Location: Kolkata

PostPosted: Thu Jan 05, 2006 3:39 pm    Post subject:
Reply with quote

Quote:
SELECT * FROM SYSIBM.SYSKEYS
WHERE IXCREATOR='<creator name>'

For IXCREATOR use the following SQL
select Name from sysibm.sysindexes where tbname=<Table Name>
Back to top
View user's profile Send private message
ragshere

New User


Joined: 20 Dec 2004
Posts: 70

PostPosted: Tue Jan 10, 2006 3:12 pm    Post subject: Hi manesh
Reply with quote

Hi Manesh,

If you didn't have access to SYSIBM.* tables it is some what tough to find primary key.

If you have DBA admin panel , you can check Primary index on that table. so that you can determine the columns involved in that index.

If you didn't have admin panel,
Then get the distinct values for each column.
for any column ,If the count matches to the row count in the table that means that column has either unique constraint or primary key.

To findout whether it is unique or primary key , try to insert a NULL value in that column. If it accepts NULL ,it is a UNIQUE column otherwise primary key.

But this technique work only when primary key is defined on only single column.

Hope you got me..

Bye
Rags
Back to top
View user's profile Send private message
iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Tue Jan 10, 2006 4:04 pm    Post subject: Re: How can I find the Primary key of a table?
Reply with quote

Hi Manesh,

Check this link for more information.

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

If you run your queries you might get -551 error which means that you do not have the Authorization to issue select statement on the catalog tables. Talk to your DBA and may be he can help out.


Hope this helps.
Back to top
View user's profile Send private message
futuredba

New User


Joined: 08 Jan 2006
Posts: 22
Location: Delhi

PostPosted: Tue Jan 10, 2006 7:42 pm    Post subject: Re: How can I find the Primary key of a table?
Reply with quote

Hi All,

We can also use FM/DB2 for this issue. Using this tool, if we open any table in edit or browse mode, then we will see 'PU' before column which is the primary key or a part of the composite primary key.
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 how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts find particular member name in PDS us... ravi243 CLIST & REXX 10 Mon Dec 19, 2016 6:44 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts How to find the first monday of the w... abdulrafi COBOL Programming 10 Fri Nov 25, 2016 3:24 pm


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