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
 

 

FINDING primary key of an existing table

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

Moderator


Joined: 24 Jul 2003
Posts: 568
Location: Bangalore

PostPosted: Wed Aug 06, 2003 8:37 am    Post subject: FINDING primary key of an existing table
Reply with quote

Dear friends,

I need to find out the primary key of an existing table. (That's not my own)
Back to top
View user's profile Send private message

mdtendulkar

Active User


Joined: 29 Jul 2003
Posts: 238
Location: USA

PostPosted: Fri Aug 08, 2003 3:53 pm    Post subject:
Reply with quote

Hello Priya,

Execute the following query, you will get the primary key for the table


SYSIBM.SYSCOLUMNS identifies columns of a parent key in column KEYSEQ; a nonzero value indicates the place of a column in the parent key.


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



SYSIBM.SYSINDEXES identifies the primary index of a table by the value P in column UNIQUERULE.


Code:
SELECT TBCREATOR, TBNAME, NAME, CREATOR, DBNAME, INDEXSPACE
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR = 'creator id'
    AND TBNAME = 'table-name'
    AND UNIQUERULE = 'P';



Hope this will help you.

Regards

Mayuresh Tendulkar
icon_lol.gif
Back to top
View user's profile Send private message
priya

Moderator


Joined: 24 Jul 2003
Posts: 568
Location: Bangalore

PostPosted: Wed Aug 13, 2003 7:53 am    Post subject: re
Reply with quote

But sir,

I have no Authorities to select from SYSIBM tables in our site. How can I find out the primary key sir...
Back to top
View user's profile Send private message
mcmillan

Site Admin


Joined: 18 May 2003
Posts: 1203
Location: India

PostPosted: Wed Aug 13, 2003 8:57 am    Post subject: Re
Reply with quote

While selecting all columns of a table,

It displayed in ASCNDING order of the primary key by default. (If you have any other parallel row in asc order, just try by inserting two more rows in des order for all columns)
Back to top
View user's profile Send private message
priya

Moderator


Joined: 24 Jul 2003
Posts: 568
Location: Bangalore

PostPosted: Fri Aug 15, 2003 3:03 pm    Post subject: Re
Reply with quote

Thanks a lot,

Can anybody (specially Mcmillan or Tendulkar) Please tell me how to find out the foreign key of a table from SYSIBM.
Back to top
View user's profile Send private message
mdtendulkar

Active User


Joined: 29 Jul 2003
Posts: 238
Location: USA

PostPosted: Fri Aug 15, 2003 3:41 pm    Post subject:
Reply with quote

Hello Priya,

Below is the answer of your query..

Code:
SELECT TBNAME, COLNAME, COLNO, CREATOR
        FROM SYSIBM.SYSFOREIGNKEYS
        WHERE TBNAME = 'table-name';



SYSFOREIGNKEYS table contains one row for every column of every foreign key.

Let me know if this helps you.

Regards

Mayuresh Tendulkar
Back to top
View user's profile Send private message
mcmillan

Site Admin


Joined: 18 May 2003
Posts: 1203
Location: India

PostPosted: Wed Aug 20, 2003 8:11 am    Post subject: Re
Reply with quote

Dear priya,

To find the relative table of that foreign key, please use SYSIBM.SYSRELS
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm


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