Catalog search for tablespaces names that have indexes.

Lee Mandel

Catalog search for tablespaces names that have indexes.
Hello List,
If I had a database name and I wanted to list only the tablespaces in that
database that had tables that had indexes what would the SQL look like to
search IBM Catalog tables.

Thanks, Lee



Dave Nance

Re: Catalog search for tablespaces names that have indexes.
(in response to Lee Mandel)
Try something like:

SELECT A.TSNAME
FROM SYSIBM.SYSTABLES A
,SYSIBM.SYSINDEXES B
WHERE A.DBNAME = '?????'
AND A.NAME = B.TBNAME
AND A.CREATOR = B.TBCREATOR


Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 01/05/01 02:20PM >>>
Hello List,
If I had a database name and I wanted to list only the tablespaces in that
database that had tables that had indexes what would the SQL look like to
search IBM Catalog tables.

Thanks, Lee






Shital S Raja

Re: Catalog search for tablespaces names that have indexes.
(in response to Dave Nance)
Lee,
try something like this

SELECT T.TSNAME, -- SYSIBM.SYSTABLES
FROM SYSIBM.SYSTABLES T, SYSIBM.SYSINDEXES I
where t.dbname = 'xxxxxxxx'
and t.name = i.tbname

thanks
Shital Raja

-----Original Message-----
From: Lee Mandel [mailto:[login to unmask email]
Sent: Friday, January 05, 2001 1:20 PM
To: [login to unmask email]
Subject: Catalog search for tablespaces names that have indexes.


Hello List,
If I had a database name and I wanted to list only the tablespaces in that
database that had tables that had indexes what would the SQL look like to
search IBM Catalog tables.

Thanks, Lee