Seriously, yes. As a general principle, one should REORG regularly and the
tablespace is titchy - only 5 million 4k pages. You'd be astonished how
quickly REORGs can run. But if you're worried about runtime, try index
REORGs with the CLEANUP ONLY option. The principle reason for REORGing is
not to find out how much space is allocated, but to reclaim it; and the
stats will be simpler, more meaningful and more susceptible to analysis if
you eliminate pseudo-empty leaf pages, pseudo-deleted RIDS etc etc., all of
which you can see in REORGCHK output.
List tablespaces tells you how much space is allocated to a tablespace
object and that figure is automatically maintained by the DBMS; for the
objects contained in the tablespace, let's look at your query and why it
might not give the whole answer:
a. you RUNSTATS at the weekend, so you have to factor in index growth
between the weekend and the time of the query;
b. the query selects only sum(NLEAF) and sum(NUM_EMPTY_LEAFS)* so what about
non-leaf pages, shown by NLEVELS? If you have a large number of small
indexes, that could make up a signifcant proportion of the whole;
c. the query does not predicate on selecting only regular or clustering
indexes (INDEXTYPE in ('REG ','CLUS')); we don't know if you have any
block, dimensional or exotic v9 XML indexes; their space behaviour is
different.
One reason for suggesting INSPECT is that like LIST TABLESPACES, it will go
straight to the underlying containers and is not dependent on stats in the
system catalog. So you're comparing like with like. INSPECT can run online
against only the one index tablespace in which you're interested, so it's
fast too.
Now, why are you surprised you have to run RUNSTATS to get stats up to date?
Yes, it would be nice if stats were maintained automatically, simultaneously
and instantaneously every time an object changed without performance impact,
but we're not there yet. Maybe V10?
Good weekend everyone, including those lucky enough to have a UK Bank
Holiday this Monday.
Alex Levy
Sustainable Software Ltd.
* Someone please tell IBM the correct plural of leaf.
-----Original Message-----
From: Henry-Sewell Brenda
[mailto:db2udbdba-ezmlmshield-x72777369.[Email address protected]
Sent: 23 May 2008 16:58
To: LazyDBA Discussion
Subject: RE: Tablespace - Used Pages
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
---------------------------------------------------------------------
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