IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

FINDING primary key of an existing table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 237
Location: USA

PostPosted: Fri Aug 08, 2003 3:53 pm
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
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: 1206
Location: India

PostPosted: Wed Aug 13, 2003 8:57 am
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
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: 237
Location: USA

PostPosted: Fri Aug 15, 2003 3:41 pm
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: 1206
Location: India

PostPosted: Wed Aug 20, 2003 8:11 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts REXX table content on panel will be r... CLIST & REXX 6
No new posts Read file names from existing file th... DFSORT/ICETOOL 6
No new posts create table with more than 32k recor... DB2 1
No new posts Finding a semi colon (;) in ISPF TSO/ISPF 5
This topic is locked: you cannot edit posts or make replies. COBOL-Common routine that contains al... COBOL Programming 4
Search our Forums:

Back to Top