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
 

 

Primary key & secondary key.

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

New User


Joined: 21 Nov 2007
Posts: 19
Location: hyderabad

PostPosted: Mon Jun 23, 2008 11:06 am    Post subject: Primary key & secondary key.
Reply with quote

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
Back to top
View user's profile Send private message

ssk1711

New User


Joined: 16 Jun 2008
Posts: 40
Location: bangalore

PostPosted: Mon Jun 23, 2008 11:19 am    Post subject:
Reply with quote

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

1)SYSIBM.SQTFOREIGNKEYS
2)SYSIBM.SQTPRIMARYKEYS
Back to top
View user's profile Send private message
Surya Kira nPasumarthi

New User


Joined: 21 Nov 2007
Posts: 19
Location: hyderabad

PostPosted: Mon Jun 23, 2008 1:39 pm    Post subject:
Reply with quote

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)
Back to top
View user's profile Send private message
ssk1711

New User


Joined: 16 Jun 2008
Posts: 40
Location: bangalore

PostPosted: Mon Jun 23, 2008 2:13 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Mon Jun 23, 2008 4:55 pm    Post subject:
Reply with quote

SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = <TABLE CREATOR NAME> AND TBNAME = <TABLE NAME> AND KEYSEQ > 0;
Back to top
View user's profile Send private message
Surya Kira nPasumarthi

New User


Joined: 21 Nov 2007
Posts: 19
Location: hyderabad

PostPosted: Mon Jun 23, 2008 5:03 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Mon Jun 23, 2008 5:26 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Surya Kira nPasumarthi

New User


Joined: 21 Nov 2007
Posts: 19
Location: hyderabad

PostPosted: Mon Jun 23, 2008 5:40 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jun 23, 2008 6:01 pm    Post subject:
Reply with quote

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



refer

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_sysibmsystablestable.htm
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Jun 23, 2008 6:05 pm    Post subject:
Reply with quote

You can even use

SELECT CONSTNAME FROM SYSIBM.SYSTABCONST WHERE TBANAME = ' TABLE NAME' AND TBCREATOR = ' CREATOR' ;
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 SFTP to primary and secondary servers mallik4u JCL & VSAM 4 Tue Jun 07, 2016 12:52 pm
No new posts SUM file with secondary sorted file cdelaf32 SYNCSORT 9 Mon Apr 25, 2016 10:20 pm
No new posts Rebuilding IMS secondary index withou... Hooman24 IMS DB/DC 6 Tue Mar 29, 2016 12:22 pm
No new posts Count unique values in a column based... Deepakgoyal2005 JCL & VSAM 4 Fri Jul 25, 2014 5:49 pm
No new posts Skipping secondary record when primar... magesh23586 DFSORT/ICETOOL 8 Sat Dec 07, 2013 11:41 pm


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