How to know the Primary and Foriegn keys?

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
User avatar
arrbee
Active Member
Posts: 144
Joined: Fri Feb 24, 2006 11:33 am

How to know the Primary and Foriegn keys?

Post by arrbee » Fri Feb 24, 2006 1:36 pm

Hi,

It is asked in an interview that how to know the Primary and Foriegn keys of a DB2 Table? As a programmer, we generally don't have the DBA authority to look at the create table?

Is it possible to find out using SPUFI (SELECT * from table;)?

Please suggest.
Arr Bee
-------------
?My joy in learning is partly that it enables me to teach? - Seneca(Roman philosopher, mid-1st century AD)

pkaushi
Member
Posts: 9
Joined: Mon Feb 06, 2006 1:45 pm

Post by pkaushi » Fri Feb 24, 2006 2:40 pm


Hi,

Theres a way to know the referential integrity constarints specified for the tables created by DBA's using the DB2Administration tool. Just type DB2A in teh command line and go browse the table. Give ddl against the table and it will list down teh sql query for the same. There you can find it out.

But if you dont use this tool Sorry will think of other alternative :-)
Thanks and Regards,
Prashant

User avatar
Kalicharan
Moderator
Posts: 31
Joined: Wed Feb 08, 2006 1:51 pm

Post by Kalicharan » Mon Mar 13, 2006 7:12 pm

Hi,

With DB2-FileAID also we can find the PRIMARY & FOREIGN keys...

Fron DB2-FileAID,
--> 3.4
--> Mention Object Name & Object Type and select TABLE
--> I command against table (I --> Information)
-->Index Key Information(In this select Unique Key for Primary Key)
--> Related Table Information to get Foreign Key Information

Thanks
Kali

User avatar
Kalicharan
Moderator
Posts: 31
Joined: Wed Feb 08, 2006 1:51 pm

Post by Kalicharan » Wed Mar 15, 2006 3:36 pm

Hi,

This another way of knowing Primary Key and Foreign Keys using DB2 SYSDBASE table (SYSIBM)

For Primary Key==>

Select NAME from SYSIBM.SYSCOLUMNS

where TBCREATOR = XXXXX and TBNAME = XXXXX

and KEYSEQ > 0 order by KEYSEQ ASC

For Foreign Keys==>

Select A.REFTBNAME ,COLNAME FROM SYSIBM.SYSFOREIGNKEYS A, SYSIBM.SYSRELS B

WHERE A.RELNAME = B.RELNAME

AND A.TBNAME = XXXXX AND A.REFTBCREATOR = XXXXX

AND A.REFTBCREATOR = B.CREATOR


Thanks
Kali.

User avatar
arrbee
Active Member
Posts: 144
Joined: Fri Feb 24, 2006 11:33 am

Post by arrbee » Thu Mar 16, 2006 10:37 am

Thanks Kalicharan. Sadly, we don't have File-Aid for DB2. I will try the other method told by you.

Thanks a lot.
Arr Bee
-------------
?My joy in learning is partly that it enables me to teach? - Seneca(Roman philosopher, mid-1st century AD)

vijay

finding the primar key....

Post by vijay » Tue May 09, 2006 5:22 am

Kalicharan wrote:Hi,

This another way of knowing Primary Key and Foreign Keys using DB2 SYSDBASE table (SYSIBM)

For Primary Key==>

Select NAME from SYSIBM.SYSCOLUMNS

where TBCREATOR = XXXXX and TBNAME = XXXXX

and KEYSEQ > 0 order by KEYSEQ ASC

For Foreign Keys==>

Select A.REFTBNAME ,COLNAME FROM SYSIBM.SYSFOREIGNKEYS A, SYSIBM.SYSRELS B

WHERE A.RELNAME = B.RELNAME

AND A.TBNAME = XXXXX AND A.REFTBCREATOR = XXXXX

AND A.REFTBCREATOR = B.CREATOR


Thanks
Kali.


hi kalicharan,

For finding primary key shd i use SYSIBM.SYSKEYS.
I am not sure abt it.will it work?

Thanks
R.Vijay

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others