Seriously, you want me to reorg every index in this tablespace to find
out how much space it is using?
Shouldn't a runstats tell me that?
We run runstats on every table & index and the syscat schema every
weekend and I expected that a runstats would give me an accurate account
of the amount of space that has been allocated to it.
In fact, I am kind of surprised that I would even have to do a runstats
to figure out how much space has been allocated to an object, it seems
odd that the database wouldn't know this always.
I can see a runstats to get the statistics on how the allocated space is
being used etc, but not to find out how much space has been allocated.
Is there really no other way to get this information?
Brenda
-----Original Message-----
From: Alex Levy
[mailto:db2udbdba-ezmlmshield-x61416235.[Email address protected]
Sent: Thursday, May 22, 2008 7:53 PM
To: LazyDBA Discussion
Subject: RE: Tablespace - Used Pages
Hi Brenda
Well it's a DMS tablespace and congratulations, your HWM corresponds
with
used pages which makes analysis a lot easier. If you run an INSPECT
after
REORGing and RUNSTATSing all indexes for the appropriate tables, that
should
give you the object ids with corresponding page allocations.
Alex Levy
Sustainable Software Ltd
-----Original Message-----
From: Henry-Sewell Brenda
[mailto:db2udbdba-ezmlmshield-x39850783.[Email address protected]
Sent: 22 May 2008 17:37
To: LazyDBA Discussion
Subject: Tablespace - Used Pages
Hi,
When I do a list tablespace show detail for this tablespace I see the
following:
--**********************************************************************
*****
Tablespace ID = 6
Name = DYNDATA1_IDX
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 5120000
Useable pages = 5119984
Used pages = 4244656
Free pages = 875328
High water mark (pages) = 4244656
Page size (bytes) = 4096
Extent size (pages) = 16
Prefetch size (pages) = 64
Number of containers = 1
--**********************************************************************
****
As you can see from this, I have 4,244,656 used pages.
This tablespace is used to store indexes only and I wanted to know which
indexes were using up all of this space, so I ran a query against the
syscat.indexes, and I sum the nleaf column and the num_empty_leafs
(shown below) you can see that they don't match at all what I am seeing
above, to the tune of about 600,000 PAGEs.
db2 "select SUM(nleaf) AS SUM_NLEAF from syscat.indexes where tbspaceid
= 6"
SUM_NLEAF
-----------
3678831
db2 "select SUM(num_empty_leafs) AS SUM_EMPTY_LEAFS from syscat.indexes
where tbspaceid = 6"
SUM_EMPTY_LEAFS
--------------------
3016
How do I find out what that other space is used for? I thought maybe it
was freespace but my tablespace usage is growing daily and the freespace
is still there. The tablespace has an extentsize of 16.
Any information you could give me would be greatly appreciated.
Brenda
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page