space reclaimed using REORG
satish mullapudi
DB2: v8/v9
OS: AIX/Win XP/2000
(In fact DB2 version & OS type/version doesn't matter much in this
context)
I have heard that disk space can be reclaimed from tables/tablespaces
by running the REORG command. I have two queries over here:
1. How to determine the "amount of free space" that will be reclaimed
by running the REORG command?
2. Can the space be reclaimed form both SMS and DMS tablespaces?
Thanks in Advance
satish
Frederik
wrote:
Well, it doesn't actually reclaim the diskspace itself, but it will
lower your tablespace's High WaterMark, which will allow you to reduce
the size of your tablespace. You will have to alter the tablespace
afterwards.
In a tablespace snapshot, you can find the current HWM. This will give
you an indication how much you can reduce the size of your tablepace.
Check the db2dart tool for hints on how to lower your HWM.
I don't think this holds up for SMS tablespaces. They will probably
shrink automatically when you lower the HWM.
--
Frederik
Ian
> Hi Friends,
> DB2: v8/v9
> OS: AIX/Win XP/2000
> (In fact DB2 version & OS type/version doesn't matter much in this
> context)
>
> I have heard that disk space can be reclaimed from tables/tablespaces
> by running the REORG command. I have two queries over here:
There are 2 issues here:
1) Table size
2) DMS Tablespace size
REORG can reduce table size. For an SMS tablespace, if the
table size is reduced, the space is immediately availble to
the operating system.
REORG may allow you to reduce the size of a DMS tablespace.
Reducing the size of the tablespace is a separate operation
(ALTER TABLESPACE ... REDUCE)
> 1. How to determine the "amount of free space" that will be reclaimed
> by running the REORG command?
If you have accurate statistics, you can look at the columns
NPAGES and FPAGES in SYSCAT.TABLES. See the documentation for
definitions of these.
Generally, the difference between NPAGES and FPAGES is how much
a table will shrink in a REORG.
> 2. Can the space be reclaimed form both SMS and DMS tablespaces?
See above.
Mark A
news:30241fda-f31c-45a8...@i8g2000pro.googlegroups.com...
It is not necessarily true that a reorg will shrink the size of the table.
It depends on whether a lot of rows have been deleted. If rows have not been
deleted, then a reorg could actually increase the size of the table because
a reorg re-establishes "percent free" (default 10% when table was created)
that may have been used up with a lot of inserts.
The Boss
wrote:
> Hi Friends,
> DB2: v8/v9
> OS: AIX/Win XP/2000
> (In fact DB2 version & OS type/version doesn't matter much in this
> context)
Actually it does:
1. DB2 on zOS is rather different from DB2 on LUW
2. v8 has restrictions that have been relieved with v9
>
> I have heard that disk space can be reclaimed from tables/tablespaces
> by running the REORG command. I have two queries over here:
> 1. How to determine the "amount of free space" that will be reclaimed
> by running the REORG command?
> 2. Can the space be reclaimed form both SMS and DMS tablespaces?
>
For an excellent article on the subject, have a look at this site:
http://www.ruban.de/DB2_luw/HWM_reduction/hwm_reduction.html
Note the article was written for v8 and therefor correctly states that
db2dart should not be run with an active database, iirc this isn't
true anymore with v9.
--
Jeroen
Satish Mullapudi
Thanks a lot for your replies friends, but I still have a query:
Is is possible (in any manner) to determine that "x amount of space
can be reclaimed from a tablespace (SMS/DMS)" BEFORE actually running
the REORG cmd. This is mainly because, if we can determine that more
amount of space (say some x mb)can be reclaimed (using the techniques
explained in the above posts) then we can take the pain of REORGing
the tables, but if we found that v.little space (say in kb) will be
reclaimed, then it is not suggestible to go through the task of
REORG!
Pls give your suggestions on the same.
Thanks in Advance
satish.
Naresh Chainani
wrote:
I think REORGCHK command is what you are looking for. From the
manuals, here is the kind of information it gives you:
# Formula F2:
For regular tables:
100*TSIZE / ((FPAGES-NPARTITIONS) * (TABLEPAGESIZE-68)) > 70
The table size in bytes (TSIZE) should be more than 70 percent of the
total space allocated for the table. (There should be less than 30%
free space.) The total space allocated for the table depends upon the
page size of the table space in which the table resides (minus an
overhead of 68 bytes). Because the last page allocated in the data
object is not usually filled, 1 is subtracted from FPAGES for each
partition (which is the same as FPAGES-NPARTITIONS).
For MDC tables:
100*TSIZE / ((ACTBLK-FULLKEYCARD) * EXTENTSIZE *
(TABLEPAGESIZE-68)) > 70
FULLKEYCARD represents the cardinality of the composite dimension
index for the MDC table. Extentsize is the number of pages per block.
The formula checks if the table size in bytes is more than the 70
percent of the remaining blocks for a table after subtracting the
minimum required number of blocks.
# Formula F3:
100*NPAGES/FPAGES > 80
The number of pages that contain no rows at all should be less than 20
percent of the total number of pages. (Pages can become empty after
rows are deleted.) As noted above, no table reorganization is
recommended when (FPAGES <= NPARTITIONS * 1 extent size). Therefore,
F3 is not calculated. For non-partitioned tables, NPARTITIONS = 1. In
a multi-partitioned database, this condition changes to FPAGES =
'number of database partitions in a database partition group of the
table' * NPARTITIONS * 1 extent size.
For MDC tables, the formula is:
100 * activeblocks / ( ( fpages / ExtentSize ) - 1 )
There are two SQL procedures to invoke REORGCHK: REORGCHK_TB_STATS and
REORGCHK_IX_STATS. I am not sure if the stored procedures are
available in v8 though.
Naresh
Satish Mullapudi
No No No..am not looking for REORGCHK & its associated formulas.
Consider this scenario:
We have planned to run the REORG cmd. For this we first run the
REORGCHK cmd. We found that REORG is necessary by this. Now, how to
determine, how much disk space can be reclaimed if we run the REORG
cmd?
I guess everybody got what am asking!
Pls comment.
satish
Arun Srinivasan
> We have planned to run the REORG cmd. For this we first run the
> REORGCHK cmd. We found that REORG is necessary by this.
advantageous (space reclaimed etc) at that
point of time, so you will get some space since reorg arranges the
pages so that only the PCTFREE space
will be left in each pages.
> Now, how to
> determine, how much disk space can be reclaimed if we run the REORG
> cmd?
The amount of pages you'll reclaim = FPAGES - NPAGES as stated above,
but you 'could' also get more because of the rearranging of rows in
pages by db2 such that it reserves only
'pctfree' to be free in each page (provided there has been deletes and
you have many gaps).
Exact amount you get out of this?? I guess it would be very hard
finding this, or else i could've written the advanced
dba certification by now ..lol
> I guess everybody got what am asking!
I guess I did :)
Arun
arunrocks at gmail dot com
Naresh Chainani
I still think the answer lies in the F2 formula (F2 = table size in
bytes as percentage of total space allocated for table) returned by
REORGCHK. The free space or the amount of space that can be reclaimed
can then be computed as:
((FPAGES-NPARTITIONS) * (TABLEPAGESIZE-68)) - TSIZE
or
((100 - F2)/100) * ((FPAGES-NPARTITIONS) * (TABLEPAGESIZE-68))
Naresh
danfan
For very small tables with few rows the formulas doesn't work very well.
Some tables always come up as reorg candidates, even just after a reorg
/dg
sandee...@gmail.com
the below command might give some idea while deciding about size
saving / reorg required. the below sql will give size in MB.
db2 "select decimal(float(t.fpages)/ ( 1024 / (b.pagesize/1024)),9,2)-
decimal(float(t.npages)/ ( 1024 / (b.pagesize/1024)),9,2) as in_mb
from syscat.tables t , syscat.tablespaces b where
t.tbspace=b.tbspace and t.tabname = 'EMPLOYEE' with ur"