Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1200
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: 1200
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts LMINIT problem - not finding DDNAME Danielle.Filteau CLIST & REXX 7 Tue Sep 19, 2017 9:57 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us