Reorg Not Reclaiming Anticipated Space

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mike_dba - 04 Jul 2006 01:20 GMT

I am using DMS raw. I have a tablespace which is defined with 5
containers each allocated at 1.5 GB for a total of about 7.5 GB. I did
a major deletion followed by a reorg and a runstats. I am not
regaining as much space as anticipated. Additionally, the
syscat.tables and syscat.indexes show my usage to be small. How do I
determine who is using all the space ?

The list tablespaces shows -
Usable pages = 7.4 GB
Used pages = 6.2 GB
Free pages to be 1.2 GB

I select from syscat.tables and see sum(fpages) = 466 MB.
I select from syscat.indexes and see sum(nleaf) = 33 MB.

The extentsize is 32 and the page size is 4096. Both data, index and
LOB use the same tablespace. SYSCAT.COLUMNS reveals that some of these
tables do have CLOB columns.

Can anyone tell me how to determine how much is really free ? Is the
used pages really this high? I am thinking not. Thanks.

Reply to this Message

Gregor Kovač - 04 Jul 2006 09:48 GMT

> I am using DMS raw. I have a tablespace which is defined with 5
> containers each allocated at 1.5 GB for a total of about 7.5 GB. I did
[quoted text clipped - 17 lines]
> Can anyone tell me how to determine how much is really free ? Is the
> used pages really this high? I am thinking not. Thanks.

Hi!

From what I know I can tell you this:
DB2 does not give the free space back to operating system. Reorg just moves
data around to have a fast access layout, depending on the previous load
DB2 had to work with.
I think that if you want the free space back then you'll have to do
EXPORT/IMPORT of your data, but I may be wrong here.

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Hardy - 04 Jul 2006 16:43 GMT

how many tables and indexes have defined on the tablespace? is it
partitoned database?

mike_dba

Reply to this Message

mike_dba - 05 Jul 2006 13:54 GMT

This particular database is only a single partition. All my tables
are in the single tablespace. There are about 200 tables and maybe 300
indexes.

The interesting thing is that there was only a slight freeing up of
space after the reorg and yet the catalog indicates that I am using
only MB (as seen by the "sum(fpages)" for all tables in the tablespace
and all indexes on these tables).

> how many tables and indexes have defined on the tablespace? is it
> partitoned database?
[quoted text clipped - 22 lines]
> > Can anyone tell me how to determine how much is really free ? Is the
> > used pages really this high? I am thinking not. Thanks.

Reply to this Message

mike - 05 Jul 2006 14:31 GMT

Using DMS raw: file or device containers?

After the bulk delete, did you also reorg all the indexes in addition
to the tables?

Are the LOB columns using COMPACT option ?

Reply to this Message

mcteard - 05 Jul 2006 16:23 GMT

> Using DMS raw: file or device containers?
>
> After the bulk delete, did you also reorg all the indexes in addition
> to the tables?
>
> Are the LOB columns using COMPACT option ?

Are you doing on-line or off-line reorg? On-line reorg does not reclaim
space for LOBS.

Reply to this Message

mike_dba - 05 Jul 2006 16:49 GMT

> > Using DMS raw: file or device containers?
> >
[quoted text clipped - 5 lines]
> Are you doing on-line or off-line reorg? On-line reorg does not reclaim
> space for LOBS.

I am using DMS raw devices. I issued a :
db2 "reorg table schema.table using other_tablespace_name"
The database was activated but there were no connections when I issued
this command.
This would make it on-line, I believe. Do need to re-run this reorg
using someother syntax? Again, the catalog shows very little used.

I quick check of the ddl using db2look reveals columns like -
"COMMENTS" CLOB(2000) LOGGED NOT COMPACT
Not sure what bearing this has.

Reply to this Message

mike_dba - 05 Jul 2006 17:08 GMT

> > > Using DMS raw: file or device containers?
> > >
[quoted text clipped - 16 lines]
> "COMMENTS" CLOB(2000) LOGGED NOT COMPACT
> Not sure what bearing this has.

I also wanted to comment, that it was my impression that the reorg
command used above would also reorg the indexes along with the table.
Is this your understanding as well ?

Reply to this Message

Pierre Saint-Jacques - 06 Jul 2006 02:52 GMT

Your reorg is an offline reorg as you do not specify INPLACE in your
command.
Your reorg then reclaims space. It does rebuild indexes by default as it is
an offline reorg.
Given the number tables and indexes in the tablespace, it is possible that
you have fragmentation of the the reorg'ed tables /indexes that leads to
high utilization of extents with loew population of same, even though you
are using a temp tablespace for the reorg.
Your reorg command also uses, by default, no reorg of the longdata. Try
again with specify LONGLOBDATA which will also implicate your CLOB's.
HTH, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

>
>> > > Using DMS raw: file or device containers?
[quoted text clipped - 21 lines]
> command used above would also reorg the indexes along with the table.
> Is this your understanding as well ?

Reply to this Message

mike_dba - 06 Jul 2006 14:05 GMT

Pierre,

Thank you for your response. I did read up on the LONGLOBDATA as you
suggested and will schedule some time to issue the reorg with this
option. Do you know if there is a way to determine how much I might
regain from previously deleted CLOBS ? The docs indicate that there is
a possibility of actually growing after a reorg of the CLOB data. I
don't think that will be the case but can't be sure.

Thank you again for your assistance.

> Your reorg is an offline reorg as you do not specify INPLACE in your
> command.
[quoted text clipped - 37 lines]
> > command used above would also reorg the indexes along with the table.
> > Is this your understanding as well ?

Reply to this Message