473,380 Members | 1,098 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,380 software developers and data experts.

Size of indexes in a particular tablespace?

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
4 2464
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Erik Hendrix | last post by:
Hi, I have a question here regarding setting the prefetch size. So far we took the rule that for OLTP, prefetchsize = extent size and for DSS prefetchsize = extent size * number. However,...
2
by: Kums | last post by:
What is the maximum permissible size of a database? Is there any limitation. What is the maximum # of tablespace's allowed in a database? Thanks for your response.
2
by: Michael Raz via DBMonster.com | last post by:
We are in SAP R/3 using z/OS DB2 V7.1 as our DB Server. SAP uses a table called VBDATA for it's internal intermediate needs just inserting in it's primary transaction a row and then selects and...
3
by: MaRCeLO PeReiRA | last post by:
Hi guys, Is there a way I can separate things in PostgreSQL? Putting tables in a disk partition and indexes in another one????? Regards, MaRcELo PeReiRa PHP/SQL/PostgreSQL
11
by: rawu | last post by:
hi , all . I got a question about creating indexes in db2 . a table has 44236333 rows an index planed to be build include 2 field ( 6 byte ) accordding to db2 document temporary...
4
by: Alvin SIU | last post by:
Hi all, I am now doing development using DB2. Q1. There is one tablespace to store 5 tables. But, when in production, there will be one tablespace for EACH of the tables.
3
by: Gregor =?UTF-8?B?S292YcSN?= | last post by:
Hi! How can I see the actual size of a table is bytes? I could to a count on table and multiply by page size, but we are using VARCHARs and I'm affraid that is not the correct value. I could...
6
by: pravin.prabhakar | last post by:
Hello, Our 4K index tablespace has grown to 64GB. It has maxed out. I want to increase the page size from 4 to 16K. What will be the safest method? Any suggestions? We are in DB2 UDB 8.2 Sun...
6
by: shawno | last post by:
Hi, We're running DB2 v8.1 on a windows platform and have a database that is quite large. It basically contains one table with a BLOB field (each blob is a zip file, maybe 500K to 1MB), and...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.