Sheth Jignesh wrote:

> I need to find out what tables, indexes and other objects that are assigned
> to a particular tablespace. Is there a command or query that I can run to
> list all the objects for a particular tablespace ?

Sheth,

All this info is in a set of standard tables in db2 (as well as oracle,
sql server, etc). Here's an example query that show tables & indexes
for a given tablespace name. You could easily add additional tables
for triggers, constraints, etc.

select SUBSTR(tables.tabschema,1,10) as tabschema ,
SUBSTR(tables.tabname,1,30) as tabname ,
tables.card ,
tables.npages ,
tables.fpages ,
SUBSTR(idx.indname,1,18) as indname ,
SUBSTR(idx.colnames,1,30) as colnames ,
tables.stats_time
from syscat.tables tables
inner join syscat.tablespaces tb
on tb.tbspaceid = tables.tbspaceid
inner join syscat.indexes idx
on tables.tabname = idx.tabname
where tb.tbspace = 'TS_SUM'
order by 1,2,6

I find queries like this extremely helpful, and recommend that anyone
who uses a database frequently gradually develop a small collection of
them. A few more examples:

To find broken views:
select name
from syscat.views
where valid <> 'Y'
order by 1

To find a column by column number (based on load error message):
select colname
from syscat.columns
where colno = 3
and lower(tabname) = 'ref_port'


Regards,

Ken

DB2 & UDB email list listserv db2-l LazyDBA home page