Find the primary key of the DB2 table.

[Apurva Shelar]

Hi,

Is there any way to find the out primary key of a DB2 table? And also the length of the every column with out using any tools, like file-aid? Please help.

[nicc]

No! You are going to least need a tool to read the DCLGEN for the table - ISPF Browse for example.

[Akatsukami]

If Apurva-kun has SELECT access on SYSIBM.SYSINDEXES, does this not become a trivial query?

[enrico-sorichetti]

And also the length of the every column with out using any tools

and SYSIBM.SYSCOLUMNS

[zprogrammer]

Please try this query ("untested")

Code: Select all

SELECT A.COLNAME,
       A.COLSEQ,
       A.ORDERING,
FROM SYSIBM.SYSKEYS A,
     SYSIBM.SYSINDEXES B
WHERE A.IXNAME = B.NAME
AND   A.IXCREATOR = B.CREATOR
AND   A.IXCREATOR = B.TBCREATOR
AND   B.TBNAME = <TABLE NAME>
AND   B.TBCREATOR = <TABLE CREATOR>
AND   B.UNIQUERULE = ‘P’

[nicc]

And hw would you use that, Pandora? Remember - no tools! (And SPUFI is a tool.)

[zprogrammer]

We need to use that by executing a DSNTIAUL program

[zprogrammer]

Or Even execute REXX (If TS has some basic knowledge to do with DSNREXX)

[Apurva Shelar]

By without any tools, I meant tools like file-aid or IBM file manager. I did not mean to go to mainframes barefooted.

Thanks Pandora-Box, your query has helped. I also looked at these two queries and they were also helpful: http://publibz.boulder.ibm.com/cgi-bin/ ... 0718164132

E.8 Retrieving catalog information about parent keys


SYSIBM.SYSCOLUMNS identifies columns of a parent key in column KEYSEQ; a nonzero value indicates the place of a column in the parent key. To retrieve the creator, database, and names of the columns in the parent key of the sample project activity table using SQL statements, execute:

Code: Select all

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

SYSIBM.SYSINDEXES identifies the primary index of a table by the value P in column UNIQUERULE. To find the name, creator, database, and index space of the primary index on the project activity table, execute:

Code: Select all

SELECT TBCREATOR, TBNAME, NAME, CREATOR, DBNAME, INDEXSPACE
       FROM SYSIBM.SYSINDEXES
       WHERE TBCREATOR = 'DSN8710'
       AND TBNAME = 'PROJACT'
       AND UNIQUERULE = 'P';

[zprogrammer]

Glad it helped