Hello,

I have a database from some tables. Each of them has a primary key.
I have to write a query to get tables, which have primary keys as an answer.
Could anyone give some ideas how to do this?

Thanks for help,
Elline

Hi,

u have to write a query with a join over sysibm.systables sysibm.syscolumns and maybe sysibm.syskeys (for the index names).

hope this will help !

regards

marc gaines

If you are on Unix, you can use the UNIQUERULE Column of the SYSCAT.INDEXES view ... UNIQUERULE is 'P' for primary keys ...

Please read 'Must Read Before Posting' thread ....

Cheers
Sathyaram

a little example (db2 for z/os):

select distinct t.name
from sysibm.systables t
inner join sysibm.syscolumns c
on t.name = c.tbname
where t.creator = c.tbcreator
and c.keyseq > 0

just an idea !

regards

marc gaines

Hi Elline

You have to join SYSIBM.SYSINDEXES and SYSIBM.SYSKEYS. For a given table and table creator (qualifier), join IXNAME and IXCREATOR columns between these two catalogs.

You will get the keys. Have the query sort by COLSEQ column of SYSKEYS.

Hope this helps

=============================================

one workaround idea that may help is to change DB2 diaglevel to 4, then use db2cc to list primary key of any table, then check db2diag.log and see the SQL used to list the primary keys, try to apply the same SQL on all tables using batch file

this donot need DB2 knowledge

donot forget to return yr diaglevel to normal ( 2 or 3 )