By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

"releasing" unused tablespace pages

P: n/a
Hello,

I have a tablespace striped across three drives, call them 1,2,3, just to
be clever. :) I allocated a lot more space (DMS) than I should have,
since I didn't know a way to estimate the space required (long story, but
the original space was in one huge tablespace for all tables ((35 million
row tables)) and I needed to segregate them out into separate tablespaces
for backup and restore purposes.

So I allocated, like 4 million 4k pages and then started to trim that
down after the data was loaded. I did an ALTER TABLESPACE ______ REDUCE
(ALL 100000) and tried to do this repeatedly until the space was
reasonable. No flame wars on whether that was the right way to do this,
it was the way I chose for expediencty.

The tablespace stats now stand at :

total pages 990000
useable 989856
used 723458
free 266368
highwater 979392
SO... I divided the free pages by 3 (number of containers) and tried to
reduce by that amount - roughly.

I am only being allowed to reduce by 9000 pages, and then it tells me :

[db2gen@hawk scripts]$ db2 "alter tablespace mqtindexspace reduce (all
9000)"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it returned:
SQL20170N There is not enough space in the table space "MQTINDEXSPACE"
for
the specified action. SQLSTATE=57059

Yet, a LIST TABLESPACES SHOW DETAIL shows :
Tablespace ID = 4
Name = MQTINDEXSPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 990000
Useable pages = 989856
Used pages = 723488
Free pages = 266368
High water mark (pages) = 979392
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 3


What gives?
M
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
P.S. I specified no freespace when I created the tablespaces

m
"mairhtin o'feannag" <ir**********@rocketmaildot.com> wrote in
news:Xn**********************************@64.164.9 8.7:
Hello,

I have a tablespace striped across three drives, call them 1,2,3, just
to be clever. :) I allocated a lot more space (DMS) than I should
have, since I didn't know a way to estimate the space required (long
story, but the original space was in one huge tablespace for all
tables ((35 million row tables)) and I needed to segregate them out
into separate tablespaces for backup and restore purposes.

So I allocated, like 4 million 4k pages and then started to trim that
down after the data was loaded. I did an ALTER TABLESPACE ______
REDUCE (ALL 100000) and tried to do this repeatedly until the space
was reasonable. No flame wars on whether that was the right way to do
this, it was the way I chose for expediencty.

The tablespace stats now stand at :

total pages 990000
useable 989856
used 723458
free 266368
highwater 979392
SO... I divided the free pages by 3 (number of containers) and tried
to reduce by that amount - roughly.

I am only being allowed to reduce by 9000 pages, and then it tells me
:

[db2gen@hawk scripts]$ db2 "alter tablespace mqtindexspace reduce (all
9000)"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned: SQL20170N There is not enough space in the table space
"MQTINDEXSPACE" for
the specified action. SQLSTATE=57059

Yet, a LIST TABLESPACES SHOW DETAIL shows :
Tablespace ID = 4
Name = MQTINDEXSPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 990000
Useable pages = 989856
Used pages = 723488
Free pages = 266368
High water mark (pages) = 979392
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 3


What gives?
M


Nov 12 '05 #2

P: n/a

"mairhtin o'feannag" <ir**********@rocketmaildot.com> wrote in message
news:Xn**********************************@64.164.9 8.6...
P.S. I specified no freespace when I created the tablespaces

m
"mairhtin o'feannag" <ir**********@rocketmaildot.com> wrote in
news:Xn**********************************@64.164.9 8.7:
Hello,

I have a tablespace striped across three drives, call them 1,2,3, just
to be clever. :) I allocated a lot more space (DMS) than I should
have, since I didn't know a way to estimate the space required (long
story, but the original space was in one huge tablespace for all
tables ((35 million row tables)) and I needed to segregate them out
into separate tablespaces for backup and restore purposes.

So I allocated, like 4 million 4k pages and then started to trim that
down after the data was loaded. I did an ALTER TABLESPACE ______
REDUCE (ALL 100000) and tried to do this repeatedly until the space
was reasonable. No flame wars on whether that was the right way to do
this, it was the way I chose for expediencty.

The tablespace stats now stand at :

total pages 990000
useable 989856
used 723458
free 266368
highwater 979392
SO... I divided the free pages by 3 (number of containers) and tried
to reduce by that amount - roughly.

I am only being allowed to reduce by 9000 pages, and then it tells me
:

[db2gen@hawk scripts]$ db2 "alter tablespace mqtindexspace reduce (all
9000)"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned: SQL20170N There is not enough space in the table space
"MQTINDEXSPACE" for
the specified action. SQLSTATE=57059

Yet, a LIST TABLESPACES SHOW DETAIL shows :
Tablespace ID = 4
Name = MQTINDEXSPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 990000
Useable pages = 989856
Used pages = 723488
Free pages = 266368
High water mark (pages) = 979392
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 3

What gives?


The free space is not neccessarily contiguous. I bet that the largest chunk
of free space in your containers (adjacent to the end of the container) is
9000 pages, and hence, the limit to your reduction in container size.

What you might want to do is run REORG on everythign. This should eliminate
free space in the middle of the objects, and therefore move all the free
space to the end of the tablespace (and containers). Then you should be
able to reduce the containers as you want.

--
Matt Emmerton
Nov 12 '05 #3

P: n/a
You cannot shrink tablespaces below the high water mark line. Try to
lower the high water mark with db2dart:
1. db2dart <dbname> /DHWM will show you which object (table, index or
SMP)holds the high water mark
Try to do a reorg on that object without specifying a temporary
tablespace - or
2. db2dart <dbname> /LHWM will show you steps how to lower the high
water mark
db2dart will ask you for your "desired highwater mark" - just answer 0
to get it shrunk to the minimum. But beware - db2dart sometimes
recommends weird steps like export table - drop it - recreate it -
load data back.

Sometimes the high water mark is held by a internal SMP (Space Map
Page) (db2dart /DHWM will tell you this). If the SMp is no longer used
it can be removed with db2dart <dbname> /RHWM. Be careful when doing
this - the database can be in backup pending afterwards.

And finally - db2dart is a offline tool - the database must not be
active while you run this tool.

HTH
Joachim

"mairhtin o'feannag" <ir**********@rocketmaildot.com> wrote in message news:<Xn**********************************@64.164. 98.6>...
P.S. I specified no freespace when I created the tablespaces

m
"mairhtin o'feannag" <ir**********@rocketmaildot.com> wrote in
news:Xn**********************************@64.164.9 8.7:
Hello,

I have a tablespace striped across three drives, call them 1,2,3, just
to be clever. :) I allocated a lot more space (DMS) than I should
have, since I didn't know a way to estimate the space required (long
story, but the original space was in one huge tablespace for all
tables ((35 million row tables)) and I needed to segregate them out
into separate tablespaces for backup and restore purposes.

So I allocated, like 4 million 4k pages and then started to trim that
down after the data was loaded. I did an ALTER TABLESPACE ______
REDUCE (ALL 100000) and tried to do this repeatedly until the space
was reasonable. No flame wars on whether that was the right way to do
this, it was the way I chose for expediencty.

The tablespace stats now stand at :

total pages 990000
useable 989856
used 723458
free 266368
highwater 979392
SO... I divided the free pages by 3 (number of containers) and tried
to reduce by that amount - roughly.

I am only being allowed to reduce by 9000 pages, and then it tells me
:

[db2gen@hawk scripts]$ db2 "alter tablespace mqtindexspace reduce (all
9000)"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned: SQL20170N There is not enough space in the table space
"MQTINDEXSPACE" for
the specified action. SQLSTATE=57059

Yet, a LIST TABLESPACES SHOW DETAIL shows :
Tablespace ID = 4
Name = MQTINDEXSPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 990000
Useable pages = 989856
Used pages = 723488
Free pages = 266368
High water mark (pages) = 979392
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 3


What gives?
M

Nov 12 '05 #4

P: n/a
As Matt indicates a REORG may help - your situation is likely being caused by the fact
that you do not have enough space above the high water mark to be able to do what you
want - reduce ... you need to have space above the high water mark ... sometimes a reorg
can help in that respect. The "reverse-rebalance" in this case is only allowed if the
number of extents being dropped by the operation is less than or equal to the number of
free extents above the HWM in the table space.
--

Bob
Engagement Specialist - DB2 Information Management Software - IBM Software Group
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official IBM position -
ask my cat!]

"Matt Emmerton" <ma**@gsicomp.on.ca> wrote in message
news:cZ********************@rogers.com...

"mairhtin o'feannag" <ir**********@rocketmaildot.com> wrote in message
news:Xn**********************************@64.164.9 8.6...
P.S. I specified no freespace when I created the tablespaces

m
"mairhtin o'feannag" <ir**********@rocketmaildot.com> wrote in
news:Xn**********************************@64.164.9 8.7:
Hello,

I have a tablespace striped across three drives, call them 1,2,3, just
to be clever. :) I allocated a lot more space (DMS) than I should
have, since I didn't know a way to estimate the space required (long
story, but the original space was in one huge tablespace for all
tables ((35 million row tables)) and I needed to segregate them out
into separate tablespaces for backup and restore purposes.

So I allocated, like 4 million 4k pages and then started to trim that
down after the data was loaded. I did an ALTER TABLESPACE ______
REDUCE (ALL 100000) and tried to do this repeatedly until the space
was reasonable. No flame wars on whether that was the right way to do
this, it was the way I chose for expediencty.

The tablespace stats now stand at :

total pages 990000
useable 989856
used 723458
free 266368
highwater 979392
SO... I divided the free pages by 3 (number of containers) and tried
to reduce by that amount - roughly.

I am only being allowed to reduce by 9000 pages, and then it tells me
:

[db2gen@hawk scripts]$ db2 "alter tablespace mqtindexspace reduce (all
9000)"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned: SQL20170N There is not enough space in the table space
"MQTINDEXSPACE" for
the specified action. SQLSTATE=57059

Yet, a LIST TABLESPACES SHOW DETAIL shows :
Tablespace ID = 4
Name = MQTINDEXSPACE
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 990000
Useable pages = 989856
Used pages = 723488
Free pages = 266368
High water mark (pages) = 979392
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 3

What gives?


The free space is not neccessarily contiguous. I bet that the largest chunk
of free space in your containers (adjacent to the end of the container) is
9000 pages, and hence, the limit to your reduction in container size.

What you might want to do is run REORG on everythign. This should eliminate
free space in the middle of the objects, and therefore move all the free
space to the end of the tablespace (and containers). Then you should be
able to reduce the containers as you want.

--
Matt Emmerton

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.