Primary key & secondary key.

Surya Kira nPasumarthi · New User Joined: 21 Nov 2007 Posts: 19 Location: hyderabad

Hi,

How do we know primay key of a table using QMF.

Is it stored any where in SYSIBM tables.

i have searched for the previous results, they are explaining using the some tools and indexes. But for the table i am searching for index is not created on the primary key. So how can we find the primay key of a table if index is not created on the primary key.

Thanks,
Surya Kiran Pasumarthi

ssk1711 · New User Joined: 16 Jun 2008 Posts: 40 Location: bangalore

The following two tables will be able to answer your requirement :

1)SYSIBM.SQTFOREIGNKEYS
2)SYSIBM.SQTPRIMARYKEYS

Surya Kira nPasumarthi · New User Joined: 21 Nov 2007 Posts: 19 Location: hyderabad

Hi ssk,

i have tried both the tables....and both tables
1)SYSIBM.SQTFOREIGNKEYS
2)SYSIBM.SQTPRIMARYKEYS

are empty and i dont have any rows and

in my database i have nearly 400-500 tables.

i believe these may be used for someother purpose..
But thank you very much for u r help.

Thanks
Surya Kiran(SK)

ssk1711 · New User Joined: 16 Jun 2008 Posts: 40 Location: bangalore

Hi SK,

The columns of the table SYSIBM.SQTPRIMARYKEYS are TABLE_CAT, TABLE_SCHEM , TABLE_NAME , COLUMN_NAME , KEY_SEQ & PK_NAME.

What I thought is, the columns TABLE_NAME & COLUMN_NAME of the table will be able to give the primary key of the table (TABLE_NAME ).

One thing, if you are checking in test region, the data may not be available. Try in production region.

the_gautam · Posted: Mon Jun 23, 2008 4:55 pm

SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = <TABLE CREATOR NAME> AND TBNAME = <TABLE NAME> AND KEYSEQ > 0;

Surya Kira nPasumarthi · New User Joined: 21 Nov 2007 Posts: 19 Location: hyderabad

hi Gautam and SSk,

I have tried both the soultion but they have not solved the problem.

I really thank all your support.

I have tried in both prod region and test region.

Regards,
Surya Kiran Pasumarthi

the_gautam · Posted: Mon Jun 23, 2008 5:26 pm

SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = <TABLE CREATOR NAME> AND TBNAME = <TABLE NAME> AND KEYSEQ > 0;

(OR)

SELECT A.COLNAME, A.COLSEQ, A.ORDERING, FROM SYSIBM.SYSKEYS A, SYSIBM.SYSINDEXES B WHERE A.IXNAME = B.NAME
AND A.IXCREATOR = B.CREATOR AND A.IXCREATOR = B.TBCREATOR AND B.TBNAME = <TAB NAME> AND B.TBCREATOR = <TAB CREATOR> AND B.UNIQUERULE = ‘P’;

i use any of these queries to get the primary key details of any table.

Surya Kira nPasumarthi · New User Joined: 21 Nov 2007 Posts: 19 Location: hyderabad

Hi Gautam,

The second u have mentioned, would work fine only if they created index on the primary key. But my query is how do we know the primary key if index is not created on the primary key.

Forexample

table stuc -- A
policy_ref_num seq_id old_policy_ref_num

Primary key of Table - A - Policy_ref_num
Index created on Table - A - for column old_policy_ref_num

And regarding the first query, i am not sure why but it nots working in our system.

Thanks,
Surya Kiran Pasumarthi

ashimer · Posted: Mon Jun 23, 2008 6:01 pm

The first query should give you want you want ... what error are you getting when running the first query ..

refer

publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_sysibmsystablestable.htm

ashimer · Posted: Mon Jun 23, 2008 6:05 pm

You can even use

SELECT CONSTNAME FROM SYSIBM.SYSTABCONST WHERE TBANAME = ' TABLE NAME' AND TBCREATOR = ' CREATOR' ;