How to find the name and value of a table's pr

问题:

How can I find the primary key column name and value of a table? I have tried looking in the SYSCAT table but cannot find anything on this.

回答1:

This should give you what you need on mainframe:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNSQH11/E.8?DT=20010718164132

DB2 10 Z/OS:

function:

SQLPrimaryKeys()

http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/odbc/src/tpc/db2z_fnprimarykeys.html

回答2:

SELECT TBCREATOR,
       TBNAME,
       NAME,
       KEYSEQ

FROM SYSIBM.SYSCOLUMNS

WHERE TBCREATOR = 'DBSCHEMA'
  AND TBNAME = 'TABLE_NAME'
  AND KEYSEQ > 0

ORDER BY KEYSEQ

回答3:

You trying to do this in a programming language like Java/Perl or at the db2 command line?

回答4:

Use the following query to list all the primary keys and foreign keys on a database:

select tbname, pkcolnames, fkcolnames from sysibm.sysrels 

For a specific table, just limit the query:

select tbname, pkcolnames, fkcolnames from sysibm.sysrels where tbname='<table name>'

I found this information at:

http://database.ittoolbox.com/groups/technical-functional/db2-l/how-to-find-a-primary-keys-and-foreign-keys-in-a-db2-table-268422