Size of indexes in a particular tablespace?

mairhtin o'feannag

Hello,

I want to ask the question "how large is each of the indexes in a
particular tablespace??" since I want to know which user-created indexes
are taking up huge amounts of space. I used the following query to
determine *WHICH* indexes are in the tablespace in question. Now I just
need to get the size of each of the indexes returned.

select cast(i1.indschema as char(15)) index_schema,
cast(i1.indname as char(25)) index_name,
cast(i1.tabschema as char(15)) table_schema,
cast(i1.tabname as char(25)) table_name,
cast(t1.index_tbspace as char(25)) index_tbspace
from syscat.indexes i1,
syscat.tables t1
where i1.indschema not like 'SYS%'
and i1.tabname = t1.tabname
and t1.index_tbspace = 'TS_INDX'
order by index_schema, index_name;
What table will give me (from syscat schema) the space used?

I'm stumped !!!!

M

Mairhtin O'Feannag

Nov 12 '05 #1

Sean McKeough

In stinger you can get the size (in pages) pages of _all_ indexes on a
snapshot for tables...all indexes sit in a single file, so it's harder
for us to track/compute the #pages per index...

mairhtin o'feannag wrote:

Hello,

I want to ask the question "how large is each of the indexes in a
particular tablespace??" since I want to know which user-created indexes
are taking up huge amounts of space. I used the following query to
determine *WHICH* indexes are in the tablespace in question. Now I just
need to get the size of each of the indexes returned.

select cast(i1.indschema as char(15)) index_schema,
cast(i1.indname as char(25)) index_name,
cast(i1.tabschema as char(15)) table_schema,
cast(i1.tabname as char(25)) table_name,
cast(t1.index_tbspace as char(25)) index_tbspace
from syscat.indexes i1,
syscat.tables t1
where i1.indschema not like 'SYS%'
and i1.tabname = t1.tabname
and t1.index_tbspace = 'TS_INDX'
order by index_schema, index_name;
What table will give me (from syscat schema) the space used?

I'm stumped !!!!

M

Mairhtin O'Feannag

Nov 12 '05 #2

mairhtin o'feannag

Sean,

OK, but the control centre has a "show estimated" size function when
selected on an index.

I have lots of indexes, so I can't just go through the CtlCtr and select
each one individually and write it down.

I feel certain that there must be a place in the catalog that contains
the size after doing a runstats. Am I on drugs? I just can't find the
location of such information, but if the Ctl Ctr can do it, so can I!!!
(I hope).

Mairhtin
Sean McKeough <mc******@nospam.ibm.com> wrote in
news:41********@news3.prserv.net:

In stinger you can get the size (in pages) pages of _all_ indexes on a
snapshot for tables...all indexes sit in a single file, so it's harder
for us to track/compute the #pages per index...

mairhtin o'feannag wrote:

Hello,

I want to ask the question "how large is each of the indexes in a
particular tablespace??" since I want to know which user-created
indexes are taking up huge amounts of space. I used the following
query to determine *WHICH* indexes are in the tablespace in question.
Now I just need to get the size of each of the indexes returned.

select cast(i1.indschema as char(15)) index_schema,
cast(i1.indname as char(25)) index_name,
cast(i1.tabschema as char(15)) table_schema,
cast(i1.tabname as char(25)) table_name,
cast(t1.index_tbspace as char(25)) index_tbspace
from syscat.indexes i1,
syscat.tables t1
where i1.indschema not like 'SYS%'
and i1.tabname = t1.tabname
and t1.index_tbspace = 'TS_INDX'
order by index_schema, index_name;
What table will give me (from syscat schema) the space used?

I'm stumped !!!!

M

Mairhtin O'Feannag

Nov 12 '05 #3

Sean McKeough

Maybe we're both on drugs. :-)

My guess would be that they're pulling this from the SYSSTAT.INDEXES
view...(there are several cardinality fields in there). Try selecting
the value from this view, and comparing it * the approximate sum of the
leaf sizes to the cc value...if they don't match I'll go bug one of the
tools guys.

mairhtin o'feannag wrote:

Sean,

OK, but the control centre has a "show estimated" size function when
selected on an index.

I have lots of indexes, so I can't just go through the CtlCtr and select
each one individually and write it down.

I feel certain that there must be a place in the catalog that contains
the size after doing a runstats. Am I on drugs? I just can't find the
location of such information, but if the Ctl Ctr can do it, so can I!!!
(I hope).

Mairhtin
Sean McKeough <mc******@nospam.ibm.com> wrote in
news:41********@news3.prserv.net:

In stinger you can get the size (in pages) pages of _all_ indexes on a
snapshot for tables...all indexes sit in a single file, so it's harder
for us to track/compute the #pages per index...

mairhtin o'feannag wrote:

Hello,

I want to ask the question "how large is each of the indexes in a
particular tablespace??" since I want to know which user-created
indexes are taking up huge amounts of space. I used the following
query to determine *WHICH* indexes are in the tablespace in question.
Now I just need to get the size of each of the indexes returned.

select cast(i1.indschema as char(15)) index_schema,
cast(i1.indname as char(25)) index_name,
cast(i1.tabschema as char(15)) table_schema,
cast(i1.tabname as char(25)) table_name,
cast(t1.index_tbspace as char(25)) index_tbspace
from syscat.indexes i1,
syscat.tables t1
where i1.indschema not like 'SYS%'
and i1.tabname = t1.tabname
and t1.index_tbspace = 'TS_INDX'
order by index_schema, index_name;
What table will give me (from syscat schema) the space used?

I'm stumped !!!!

M

Mairhtin O'Feannag

Nov 12 '05 #4

Sean McKeough

Or maybe numrids...(gotta be one of the fields in that view)...

Sean McKeough wrote:

Maybe we're both on drugs. :-)

My guess would be that they're pulling this from the SYSSTAT.INDEXES
view...(there are several cardinality fields in there). Try selecting
the value from this view, and comparing it * the approximate sum of the
leaf sizes to the cc value...if they don't match I'll go bug one of the
tools guys.

mairhtin o'feannag wrote:

Sean,

OK, but the control centre has a "show estimated" size function when
selected on an index.

I have lots of indexes, so I can't just go through the CtlCtr and
select each one individually and write it down.

I feel certain that there must be a place in the catalog that contains
the size after doing a runstats. Am I on drugs? I just can't find
the location of such information, but if the Ctl Ctr can do it, so can
I!!! (I hope).

Mairhtin
Sean McKeough <mc******@nospam.ibm.com> wrote in
news:41********@news3.prserv.net:

In stinger you can get the size (in pages) pages of _all_ indexes on a
snapshot for tables...all indexes sit in a single file, so it's harder
for us to track/compute the #pages per index...

mairhtin o'feannag wrote:
Hello,

I want to ask the question "how large is each of the indexes in a
particular tablespace??" since I want to know which user-created
indexes are taking up huge amounts of space. I used the following
query to determine *WHICH* indexes are in the tablespace in question.
Now I just need to get the size of each of the indexes returned.

select cast(i1.indschema as char(15)) index_schema,
cast(i1.indname as char(25)) index_name,
cast(i1.tabschema as char(15)) table_schema,
cast(i1.tabname as char(25)) table_name,
cast(t1.index_tbspace as char(25)) index_tbspace
from syscat.indexes i1,
syscat.tables t1
where i1.indschema not like 'SYS%'
and i1.tabname = t1.tabname
and t1.index_tbspace = 'TS_INDX'
order by index_schema, index_name;
What table will give me (from syscat schema) the space used?

I'm stumped !!!!

M

Mairhtin O'Feannag

Nov 12 '05 #5