9

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.

10

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';
  • 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
8

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
  • 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
0

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;
  • 2
    The primary key is not an index (although it is supported by one). – mustaccio May 28 '14 at 19:29
0
  1. SYSCAT.INDEXES.UNIQUERULE ( If UNIQUERULE is P then primary Key )
  2. SYSCAT.TABCONST.TYPE ( if Type is P then Primary Key Constraint )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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