Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

To view list of tables for particular column

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Ashok09

New User


Joined: 29 Mar 2009
Posts: 20
Location: Chennai

PostPosted: Mon Dec 07, 2009 12:55 pm    Post subject: To view list of tables for particular column
Reply with quote

Hi,

Quest1: Consider i have column CITY, but i dont know in which all tables the CITY column available. what is the sql query to retrieve the list of tables for a particular column.

Quest2: how to view the index which are created already?

Let me know, if quest are not clear enough

Thanks in Advance!!
Back to top
View user's profile Send private message

PeD

Active User


Joined: 26 Nov 2005
Posts: 456
Location: Belgium

PostPosted: Mon Dec 07, 2009 1:00 pm    Post subject:
Reply with quote

Q1 : SYSIBM.SYSCOLUMNS

Q2 : not clear
Back to top
View user's profile Send private message
deepthimadhu

New User


Joined: 03 Mar 2009
Posts: 12
Location: Trivandrum

PostPosted: Mon Dec 07, 2009 1:10 pm    Post subject:
Reply with quote

You can try this for question no: 1

SELECT *
FROM SYSIBM.SYSCOLUMNS
WHERE NAME = 'CITY';
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2435
Location: Netherlands, Amstelveen

PostPosted: Mon Dec 07, 2009 1:20 pm    Post subject:
Reply with quote

Q2 : SYSIBM.SYSINDEXES table
Back to top
View user's profile Send private message
Ashok09

New User


Joined: 29 Mar 2009
Posts: 20
Location: Chennai

PostPosted: Mon Dec 07, 2009 3:07 pm    Post subject:
Reply with quote

@deepthimadhu
Quote:
SELECT *
FROM SYSIBM.SYSCOLUMNS
WHERE NAME = 'CITY'


The above query list only the columns available in all table, but my question is "to retrieve the list of tables for a particular column". so the query output should be the table names.


Quest 2:
I want to view the content of a particular index. Is tat possible?

SYSIBM.SYSINDEXES wil list the index which are created.

Thank you.
Back to top
View user's profile Send private message
deepthimadhu

New User


Joined: 03 Mar 2009
Posts: 12
Location: Trivandrum

PostPosted: Mon Dec 07, 2009 4:13 pm    Post subject:
Reply with quote

[The above query list only the columns available in all table, but my question is "to retrieve the list of tables for a particular column". so the query output should be the table names. ]

Please try running the query before replying...The WHERE clause contains the particular column name 'CITY'. The query o/p would contain the table names along with many other information for the column 'CITY'.

[/quote]
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Mon Dec 07, 2009 4:16 pm    Post subject:
Reply with quote

Quote:
The above query list only the columns available in all table, but my question is "to retrieve the list of tables for a particular column". so the query output should be the table names.


No, you are wrong.

If you don't want all the columns for each row that represents tables (qualified by creator) containing a column 'CITY', modify the SELECT * to SELECT TBNAME.

SYSCOLUMNS
Back to top
View user's profile Send private message
Ashok09

New User


Joined: 29 Mar 2009
Posts: 20
Location: Chennai

PostPosted: Tue Dec 08, 2009 3:20 pm    Post subject:
Reply with quote

yes i got answer for quest1.

what is the query for
Quest 2:
I want to view the content of a particular index
Back to top
View user's profile Send private message
Ashok09

New User


Joined: 29 Mar 2009
Posts: 20
Location: Chennai

PostPosted: Tue Dec 08, 2009 5:47 pm    Post subject:
Reply with quote

frnds got solution for Quest 2 as well.

Thank you....
Back to top
View user's profile Send private message
raja.j

New User


Joined: 10 Dec 2009
Posts: 1
Location: Bangalore

PostPosted: Thu Dec 10, 2009 2:49 pm    Post subject: Reply to: To view list of tables for particular column
Reply with quote

Q1. select * fom sysibm.syscolums.

Q2. Select * from sysibm.sysindex.
Back to top
View user's profile Send private message
rexx77

New User


Joined: 14 Apr 2008
Posts: 74
Location: Mysore

PostPosted: Mon Dec 14, 2009 7:50 pm    Post subject:
Reply with quote

Quote:

Q1. select * fom sysibm.syscolums.

Q2. Select * from sysibm.sysindex


I greatly appreciate for your effort to answer the question, but did you check the tables you have mentioned before submitting the answer.

it is SYSCOLUMNS, SYSINDEXES respectively.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts REXX to view the PGM or PROC jackzhang75 CLIST & REXX 11 Tue Jun 20, 2017 1:20 am
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts member list compare jzhardy TSO/ISPF 6 Fri May 12, 2017 3:18 pm
No new posts How to see VIEW definition from BMC c... rohanthengal DB2 1 Thu May 04, 2017 1:50 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us