| View previous topic :: View next topic |
| Author |
Message |
sivafdms
Intermediate
Joined: 29 May 2007
Posts: 165
Topics: 77
|
 Posted: Tue Jun 26, 2007 1:32 am Post subject: To find Primary keys and foreign keys ,structure of table |
 |
|
Hi all,
I am new to mainframes and DB2.Could any one tell me how we can find the primary keys and foriegn keys, if i only the name on the table.
I want the query where we can find the primary keys and foreign keys of the table.And how can we find the structure of the table with out the help of the DCLGEN
Tks,
Siva |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
|
| Back to top |
|
|
bauer
Intermediate
Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany
|
| Posted: Wed Jun 27, 2007 6:16 am Post subject: |
|
|
sivafdms,
structure of the table:
| Code: |
select
tab.dbname
,tab.creator
,substr(tab.name,1,8) as name
,tab.card
,tab.npages
,substr(tab.remarks,1,20) as remark
,substr(col.name,1,8) as colname
, col.length as length
,col.coltype
,col.keyseq
,col.NULLs
,col.remarks
from sysibm.systables TAB
INNER JOIN SYSIBM.SYSCOLUMNS COL
ON TAB.NAME = COL.TBNAME
AND TAB.creator = COL.TBCREATOR
where TAB.name = 'mytable'
and TAB.type = 'T'
AND TAB.CREATOR = 'mycreator'
order by tab.creator
,tab.name
,col.colno
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 27, 2007 6:28 am Post subject: |
|
|
bauer,
I would change the Col.name to 18(<= v7) or 30(v8 ) bytes instead of 8 bytes. I would also add COL.SCALE after coltype to take care of Decimals if any.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
bauer
Intermediate
Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany
|
| Posted: Wed Jun 27, 2007 7:43 am Post subject: |
|
|
kolusu,
yes, your are right for "long" columnnames.
The advantage of reducing to 8 digits is, to see all (my be truncated) information on one screen without paging left / right. But ok, this depends on the screen .....
Have a nice day,
bauer |
|
| Back to top |
|
|
bstillwa
Beginner
Joined: 01 Nov 2004
Posts: 23
Topics: 6
Location: NJ
|
| Posted: Fri Jun 29, 2007 11:41 am Post subject: |
|
|
Just use DB2TOOLS, which is the DB2 Administration Tool. This should be available for any developer to use. In our shop, we access it via TSO DB2TOOLS. Much better than using a cumbersome select.
_________________
Bev. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Sun Jul 01, 2007 7:00 pm Post subject: |
|
|
| bstillwa wrote: | | Just use DB2TOOLS, which is the DB2 Administration Tool. This should be available for any developer to use. In our shop, we access it via TSO DB2TOOLS. Much better than using a cumbersome select. |
DB2 TOOLS is an add on product and is not available in all shops.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
