Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are using a tool that requires specific tables in our DB2 database to have a Primary Key defined.

Is there a way using a select statement on the DB to see if a given table has one?

Thanks.

share|improve this question

3 Answers 3

up vote 7 down vote accepted

Disclaimer: I do not know DB2.

I simply googled these with "db2 table definition".

Source:

SELECT * 
FROM SYSIBM.SYSTABLES TAB,SYSIBM.SYSCOLUMNS COL 
WHERE TAB.CREATOR = COL.TBCREATOR 
AND TAB.CREATOR = 'xxxx' 
AND TAB.NAME = 'xxxxxxxxxxxxx' 
AND TAB.NAME = COL.TBNAME 
AND TAB.TYPE = 'V' ( OR 'T' ) 
ORDER BY 1,2;

Source:

SELECT * FROM syscat.tabconst WHERE type = 'P';
share|improve this answer
1  
TAB.TYPE = 'V' will give you views, which I believe you do not want. Use TAB.TYPE='T' for tables. –  GilShalit Jun 19 '12 at 14:53

you can do a db2look, which will give you the ddls for the table.

db2look -d db_name -e -x -z schema_name -t table_name
share|improve this answer
    
Welcome to dba.se and thanks for this helpful answer - I hope you don't mind my formatting edit? –  Jack Douglas Oct 25 '11 at 10:02
    
@JackDouglas -- thanks for those edits, looks pretty readable now –  Govind Kailas Oct 25 '11 at 12:09

This is probably the easiest option, since a primary key is supported by a matching index:

select COLNAMES from SYSIBM.SYSINDEXES where tbname = 'TABLE' and uniquerule = 'P';

You can also query the columns catalog table:

select NAME from SYSIBM.SYSCOLUMNS where tbname = 'TABLE' and keyseq > 0 order by keyseq;
share|improve this answer
2  
The primary key is not an index (although it is supported by one). –  mustaccio May 28 at 19:29
    
Thanks, updated that. –  smackfu May 28 at 19:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.