Page 1 of 1
Find the primary key of the DB2 table.
Posted: Tue Oct 13, 2015 2:54 pm
by 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.
Re: Find the primary key of the DB2 table.
Posted: Tue Oct 13, 2015 5:13 pm
by nicc
No! You are going to least need a tool to read the DCLGEN for the table - ISPF Browse for example.
Re: Find the primary key of the DB2 table.
Posted: Tue Oct 20, 2015 11:42 pm
by Akatsukami
If Apurva-kun has SELECT access on SYSIBM.SYSINDEXES, does this not become a trivial query?
Re: Find the primary key of the DB2 table.
Posted: Wed Oct 21, 2015 12:15 am
by enrico-sorichetti
And also the length of the every column with out using any tools
and SYSIBM.SYSCOLUMNS
Re: Find the primary key of the DB2 table.
Posted: Wed Oct 28, 2015 1:38 pm
by 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’
Re: Find the primary key of the DB2 table.
Posted: Wed Oct 28, 2015 6:57 pm
by nicc
And hw would you use that, Pandora? Remember - no tools! (And SPUFI is a tool.)
Re: Find the primary key of the DB2 table.
Posted: Wed Oct 28, 2015 7:49 pm
by zprogrammer
We need to use that by executing a DSNTIAUL program
Re: Find the primary key of the DB2 table.
Posted: Wed Oct 28, 2015 7:53 pm
by zprogrammer
Or Even execute REXX (If TS has some basic knowledge to do with DSNREXX)
Re: Find the primary key of the DB2 table.
Posted: Mon Nov 02, 2015 2:22 pm
by 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';
Re: Find the primary key of the DB2 table.
Posted: Mon Nov 02, 2015 3:48 pm
by zprogrammer
Glad it helped