473,326 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

"releasing" unused tablespace pages

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
4 14349
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

"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: Jane Austine | last post by:
As far as I know python's threading module models after Java's. However, I can't find something equivalent to Java's interrupt and isInterrupted methods, along with InterruptedException....
36
by: Andrea Griffini | last post by:
I did it. I proposed python as the main language for our next CAD/CAM software because I think that it has all the potential needed for it. I'm not sure yet if the decision will get through, but...
16
by: aurora | last post by:
Hello! Just gone though an article via Slashdot titled "The Free Lunch Is Over: A Fundamental Turn Toward Concurrency in Software" http://www.gotw.ca/publications/concurrency-ddj.htm]. It argues...
7
by: Evan Simpson | last post by:
WEBoggle needs a new game board every three minutes. Boards take an unpredictable (much less than 3min, but non-trivial) amount of time to generate. The system is driven by web requests, and I...
77
by: Jon Skeet [C# MVP] | last post by:
Please excuse the cross-post - I'm pretty sure I've had interest in the article on all the groups this is posted to. I've finally managed to finish my article on multi-threading - at least for...
7
by: Willem van Rumpt | last post by:
Hi all, coming from an unmanaged programming background, I took my time to sort out the IDisposable and finalizer patterns. Just when I thought I had it all conceptually neatly arranged, the...
25
by: Koliber (js) | last post by:
sorry for my not perfect english i am really f&*ckin angry in this common pattern about dispose: ////////////////////////////////////////////////////////// Public class...
94
by: Samuel R. Neff | last post by:
When is it appropriate to use "volatile" keyword? The docs simply state: " The volatile modifier is usually used for a field that is accessed by multiple threads without using the lock...
30
by: Medvedev | last post by:
i see serveral source codes , and i found they almost only use "new" and "delete" keywords to make they object. Why should i do that , and as i know the object is going to be destroy by itself at...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.