Reorg Not Reclaiming Anticipated Space

mike_dba

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.

Jul 4 '06 #1

Gregor Kova�

mike_dba wrote:

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.

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

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Jul 4 '06 #2

Hardy

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

mike_dba 写�：

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.

Jul 4 '06 #3

mike_dba

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).
Hardy wrote:

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

mike_dba 写�：

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.

Jul 5 '06 #4

mike

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 ?

Jul 5 '06 #5

mcteard

mike wrote:

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.

Jul 5 '06 #6

mike_dba

mcteard wrote:

mike wrote:

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.

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.

Jul 5 '06 #7

mike_dba

mike_dba wrote:

mcteard wrote:

mike wrote:

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.

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.

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 ?

Jul 5 '06 #8

Pierre Saint-Jacques

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.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"mike_dba" <mi*************@yahoo.coma écrit dans le message de news:
11**********************@l70g2000cwa.googlegroups. com...

>
mike_dba wrote:

>mcteard wrote:

mike wrote:
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.

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.

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 ?

Jul 6 '06 #9

mike_dba

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.
Pierre Saint-Jacques wrote:

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 itis
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.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"mike_dba" <mi*************@yahoo.coma écrit dans le message de news:
11**********************@l70g2000cwa.googlegroups. com...

mike_dba wrote:

mcteard wrote:
mike wrote:
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.
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.

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 ?

Jul 6 '06 #10