|
View previous topic :: View next topic
|
| Author |
Message |
tosaurabh20
New User
Joined: 08 Jun 2007
Posts: 26
Location: Noida
|
|
|
|
Hi Al.
Can anyone tell me how to find out the primary key of the table provided with the name of that table??
Let me know in case further details are required.
Thanks
Saurabh |
|
| Back to top |
|
 |
murmohk1
Senior Member
.jpg)
Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India
|
|
|
|
Saurabh,
| Quote: |
| Can anyone tell me how to find out the primary key of the table provided with the name of that table?? |
Have you heard of sys tables? |
|
| Back to top |
|
|
tosaurabh20
New User
Joined: 08 Jun 2007
Posts: 26
Location: Noida
|
|
|
|
| murmohk1 wrote: |
Saurabh,
| Quote: |
| Can anyone tell me how to find out the primary key of the table provided with the name of that table?? |
Have you heard of sys tables? |
I have heard about them but do let me know where it will specifically show that this column is a primary key for a table?
Your quick response is appreciable.
Thanks
Saurabh |
|
| Back to top |
|
|
ParagChouguley
Active User
.jpg)
Joined: 03 Feb 2007
Posts: 175
Location: PUNE(INDIA)
|
|
|
|
Hi tosaurabh20,
First go to table SYSIBM.SYSINDEXES and obtain index name and its creator for your table. If multiple indexes found, select the index which is restricted with unique value clause(means group of columns associated with that index will have unique value)
Then go to table SYSIBM.SYSKEYS and obtain columns associated to that INDEX and creator combination.
--Parag |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| ParagChouguley wrote: |
| table SYSIBM.SYSINDEXES and obtain index name |
Hi,
Is it a valid table name, second qualifier (SYSINDEXES) contains 10-characters. I couldn't find this table in my system. Please suggest some other way. |
|
| Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India
|
|
|
|
Anuj,
| Quote: |
| Is it a valid table name, second qualifier (SYSINDEXES) contains 10-characters |
Yes, it is valid. |
|
| Back to top |
|
|
kussu
Warnings : 1
New User
Joined: 16 May 2007
Posts: 33
Location: India
|
|
|
|
Hi,
Are you using platinum tool for accessing DB2 tables???
If so, there you can easily find out the Primary key for the given table
The primary key columns are mentioned with different color , |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| Or if you have ADBL and have permissions to it. We have to run it as TSO @ADBL, pick the DB2 system we want to access. Select 1, then T to get a list of tables (fill in selection criteria at the bottom if you want to restrict the returned tables). On the line with the table you want select with an X. Look for the row that has the column U filled in with P. Select that line with a C. You should see your primary key columns. |
|
| Back to top |
|
|
pankajsoni
New User
.jpg)
Joined: 21 Jun 2006
Posts: 9
Location: Zurich
|
|
|
|
Use the below query: (change the tabname & creator in the last line)
| Code: |
SELECT
A.NAME,A.TBNAME,A.TBCREATOR,A.COLTYPE,
A.LENGTH,A.COLNO,A.NULLS,
CASE
WHEN D.RELNAME = C.RELNAME
THEN D.REFTBCREATOR!!'.'!!D.REFTBNAME!!'('!!E.NAME!!')'
ELSE ''
END AS REF,
CASE
WHEN A.NAME = B.COLNAME THEN 'P'
WHEN A.NAME = C.COLNAME THEN 'F'
ELSE ''
END AS KEY
FROM SYSIBM.SYSCOLUMNS AS A
LEFT OUTER JOIN SYSIBM.SYSKEYCOLUSE AS B
ON A.NAME = B.COLNAME
AND A.TBCREATOR = B.TBCREATOR
AND A.TBNAME = B.TBNAME
LEFT OUTER JOIN SYSIBM.SYSFOREIGNKEYS AS C
ON C.CREATOR = A.TBCREATOR
AND C.TBNAME = A.TBNAME
AND C.COLNAME = A.NAME
LEFT OUTER JOIN SYSIBM.SYSRELS AS D
ON D.CREATOR = C.CREATOR
AND D.TBNAME = C.TBNAME
AND D.RELNAME = C.RELNAME
LEFT OUTER JOIN SYSIBM.SYSCOLUMNS AS E
ON E.TBNAME = D.REFTBNAME
AND E.TBCREATOR = D.REFTBCREATOR
AND C.COLSEQ = E.KEYSEQ
WHERE A.TBCREATOR='XXXX' AND A.TBNAME='XXXXXXXX' ORDER BY A.COLNO |
|
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| murmohk1 wrote: |
| Yes, it is valid. |
Thanks for the clarification, but I couldn't get the that table-name in my system. Can you please suggest some other table-name for the same purpose.
Kussu,
No, we are not using platinum tool in our system.
stodolas,
What is ADBL? TSO @ADBL does not work for me. In my system I usually use TSO BMCADM to invoke DB2 related utilities. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| ADBL is the DB2 admin tool. You may not have permissions to run it. Check with your DBAs |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| stodolas wrote: |
| Check with your DBAs |
This is one drawback when you work from offshore, we don't have a direct access to our system DBAs, any alternate way to find whether these type of tools (ADBL) are installed on our site & me, as an end user, can use them. |
|
| Back to top |
|
|
socker_dad
Active User
Joined: 05 Dec 2006
Posts: 177
Location: Seattle, WA
|
|
|
|
| You could try emailing you DBAs. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
pankajsoni: That is a very very convuluted query to get the primary key
for a table.
Try using the following. It should be much easier to follow.
| Code: |
SELECT SI.TBCREATOR, SI.TBNAME, SK.COLNAME, SK.COLSEQ
FROM SYSIBM.SYSINDEXES SI, SYSIBM.SYSKEYS SK
WHERE SI.NAME = SK.IXNAME
AND SK.IXCREATOR = SI.TBCREATOR
AND SI.TBCREATOR = 'XXXX'
AND SI.TBNAME = 'YYYY'
AND SI.UNIQUERULE = 'P'
--Puts them in the order they are in the PK
ORDER BY COLSEQ
WITH UR;
|
Replace XXXX with the table creator and YYYY with the actual table name.
If you don't have XXXX you can leave that off and get all tables in that
DB2 subsystem with that name.
anuj_model: I can only find my DBA if I walk across the campus and hunt
them down and then hopefully I picked a good time so they aren't in a
meeting. Email them. Someone has to be maintaing your system and be able
to give you direction as to what tools you do and don't have availbable. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| socker_dad wrote: |
| You could try emailing you DBAs. |
Yeah, this is one way, I'll try.
| todolas wrote: |
| I walk across the campus and hunt them down and then hopefully I picked a good time so they aren't in a meeting. |
I cann't just walk from India to US/ Florida  (I guess, our DBAs work from there). You got enough advantages to hunt your DBAs in your own campus 
| todolas wrote: |
| Email them. |
Yeah, I'll try this & will be back to you guys, if they ask something new.
Thanks for the suggestion, let me take my time. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 631
Location: Wisconsin
|
|
|
|
| My DBA also has a reputation for not taking your calls except between 10 am and 2 pm as long as he isn't on lunch or break in there. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Hi,
You've taken it otherwise man, I just wanted to say that I 'm located far enough from my DBAs' work location. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
the appendixes in any DB2 SQL Application Programmers Guide has a list of all the SYSIBM tables, their construct, and examples of queries against them.
look in the manuals!!!!!!!!!!!!!!!!!!! |
|
| Back to top |
|
|
|
|
