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 4 2466
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
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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,...
|
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.
|
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...
|
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
|
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...
|
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.
|
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...
|
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...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
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: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |