DB2 Table Layout Question 1

[trmweb]

I'm trying to gather DB2 table/column layout information to build a data dictionary. I'm doing this on the mainframe with SPUFI because I lack third party software like BMC or Candle. I'm okay SELECTing from SYSIBM.SYSCOLUMN & SYSIBM.SYSTABLE tables. My question is -

How do I determine the primary key columns?
Any one have an example of gathering this info?

Thanks!
TRMWEB

 

[staland]

Hello,

Try this:

SELECT A.NAME,B.NAME AS PRIMKEY
FROM SYSIBM.SYSTABLES A, SYSIBM.SYSCOLUMNS B
WHERE A.NAME = B.TBNAME
AND A.KEYCOLUMNS = B.COLNO ;

Helle

 

[trmweb]

staland - that is exactly what I was looking for... thanks ... you get a star!

 

[trmweb]

Following is the SQL I used to get what I needed --

SELECT TBCREATOR, TBNAME, NAME AS COLUMN_NAME,
KEYSEQ AS SEQ_#_OF_COL_IN_PK
FROM SYSIBM.SYSCOLUMNS

WHERE KEYSEQ > 0
AND TBNAME = 'table_name'
AND TBCREATOR = 'table_creator'
ORDER BY TBCREATOR, TBNAME, SEQ_#_OF_COL_IN_PK
;