How to find primary key on a table in DB2?

Question

I am unable to fetch primary key in DB2. I used following code but It is not working for me.

SELECT TBCREATOR, TBNAME, NAME, KEYSEQ FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'DSN8710' AND TBNAME = 'EMPLOYEE' AND KEYSEQ > 0 ORDER BY KEYSEQ;

And what is the means of TBCREATOR in this code and how to modified TBCREATOR value according to my case?

Answer 1

I'll answer your last question first. creator is sometimes referred to as schema. If you're familiar with Oracle, this is roughly analogous to a database user (though not exactly).

As far as getting the "primary key" information, you probably want to know which index is the "clustering" index (which is what usually, but not always, determines the physical ordering of the rows on disk).

How you find the clustering index depends on the platform you're running:

Mainframe (z/OS):

SELECT
     RTRIM(name)    AS index_name
    ,RTRIM(creator) AS index_schema
    ,uniquerule
    ,clustering
FROM sysibm.sysindexes
WHERE tbname     = @table
  AND tbcreator  = @schema
  AND clustering = 'Y'

Then, to see the actual columns in that index, you perform this query:

SELECT colname AS name
FROM sysibm.sysindexes a
JOIN sysibm.syskeys b
    ON a.name       = b.ixname 
    AND a.tbcreator = b.ixcreator 
WHERE a.name        = @index_name
    AND a.tbcreator = @index_schema
ORDER BY COLSEQ

Linux/Unix/Windows:

SELECT
     RTRIM(indname)   AS index_name
    ,RTRIM(indschema) AS index_schema
    ,uniquerule
    ,indextype
FROM syscat.indexes
WHERE tabname     = @table
  AND tabschema = @schema
  AND indextype = 'CLUS'

Then, to see the actual columns in that index, you perform this query:

SELECT colnames as name
FROM sysibm.sysindexes
WHERE name        = @index_name
    AND tbcreator = @index_schema
ORDER BY NAME

LUW returns the list of columns as one string, delimited by +, which is kind of weird...