Reputation: 13
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?
Upvotes: 0
Views: 19056
Reputation: 15499
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:
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
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...
Upvotes: 1