How can I find the Primary key of a table?

manesh · New User Joined: 18 May 2003 Posts: 7 Location: Bangalore

Dear Guys,

Can anybody clear:

How to identify my primary key of the table, if I don't have permission to view SYSIBM.* Tables............

mcmillan · Site Admin Joined: 18 May 2003 Posts: 1210 Location: India

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.

dhan_chander · New User Joined: 26 Feb 2004 Posts: 1 Location: Chennai

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

uvraman · New User Joined: 05 Mar 2004 Posts: 2 Location: Bangalore

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.

Jaya kishan Y · New User Joined: 04 Jan 2006 Posts: 1 Location: Trivandrum

hai guys,

what is the exact reason for status code 4

lkhiger · New User Joined: 28 Oct 2005 Posts: 89

By using the "Platinum"

winchaat · New User Joined: 23 Mar 2005 Posts: 14

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

hope this helps...

---->RAG

sairam · New User Joined: 30 Aug 2005 Posts: 58 Location: Kolkata

ragshere · New User Joined: 20 Dec 2004 Posts: 70

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

iknow · Posted: Tue Jan 10, 2006 4:04 pm

Hi Manesh,

Check this link for more information.

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.

futuredba · New User Joined: 08 Jan 2006 Posts: 22 Location: Delhi

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.