473,320 Members | 1,900 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,320 software developers and data experts.

Tablespace remains full after deletions

Hello,

A DB2 v. 8 DMS tablespace ran full. Subsequently, almost all data in the
tablespace has been deleted, but "LIST TABLESPACES SHOW DETAIL" keeps
stating that the tablespace has 0 free pages. I've tried running a REORG
on all remaining tables; that didn't help.

Trying to create a table in the tablespace yields:
bash-3.00$ db2 "create table foobar (x int not null) in s_problemspace"
DB21034E The command was processed as an SQL statement because it
was not a valid Command Line Processor command. During SQL processing
it returned:
SQL0289N Unable to allocate new pages in table space "S_PROBLEMSPACE".
SQLSTATE=57011

How can I make DB2 realize that there is plenty of space in the
tablespace?

"Dropped table recovery" is activated for the tablespace, by the way.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Aug 6 '07 #1
3 4428
"Troels Arvin" <tr****@arvin.dkwrote in message
news:pa*********************@arvin.dk...
Hello,

A DB2 v. 8 DMS tablespace ran full. Subsequently, almost all data in the
tablespace has been deleted, but "LIST TABLESPACES SHOW DETAIL" keeps
stating that the tablespace has 0 free pages. I've tried running a REORG
on all remaining tables; that didn't help.

Trying to create a table in the tablespace yields:
bash-3.00$ db2 "create table foobar (x int not null) in s_problemspace"
DB21034E The command was processed as an SQL statement because it
was not a valid Command Line Processor command. During SQL processing
it returned:
SQL0289N Unable to allocate new pages in table space "S_PROBLEMSPACE".
SQLSTATE=57011

How can I make DB2 realize that there is plenty of space in the
tablespace?

"Dropped table recovery" is activated for the tablespace, by the way.

--
Regards,
Troels Arvin
The tablespace should have space for new rows in the same table where you
deleted some rows (unless your table is defined with append). But deleting
rows does not necessarily free up any pages if there is still at least one
row on the page.

In addition, once a table has used a page, it may not give that page to a
different table even if the page is now empty, until after a reorg on the
table with empty pages. I am not 100% sure about this one, so hopefully
someone else will comment).

By reorging your other tables, you may have inadvertently used more pages
since the percent free space is created after a reorg.
Aug 6 '07 #2
On Mon, 06 Aug 2007 06:33:00 +0000, I wrote:
A DB2 v. 8 DMS tablespace ran full. Subsequently, almost all data in the
tablespace has been deleted, but "LIST TABLESPACES SHOW DETAIL" keeps
stating that the tablespace has 0 free pages.
Never found the cause of the problem, nor an elegant way to solve it.

Workaround became:
1. Saved structure of remaining (few and small) tables in the
tablespace, using db2look.
2. Dumped the contents of the tables to flat IXF files using db2move.
3. Dropped the tablespace and re-created it.
4. Re-created the tables, using the sql file created in step 1.
5. Imported the table contents again, using db2move.
6. Voilá: Lots of free pages in the tablespace again.

Strange.

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Aug 7 '07 #3
On Tue, 07 Aug 2007 15:22:16 +0000, Troels Arvin wrote:
Workaround became:
1. Saved structure of remaining (few and small) tables in the
tablespace, using db2look.
2. Dumped the contents of the tables to flat IXF files using db2move. 3.
Dropped the tablespace and re-created it. 4. Re-created the tables,
using the sql file created in step 1. 5. Imported the table contents
again, using db2move. 6. Voilá: Lots of free pages in the tablespace
again.
And subsequently, daily incremental backups failed for reasons that I
could not identify.

The next time I add a new tablespace to a database which uses incremental
backups, I'll do an immediate, full backup of the new tablespace...

--
Regards,
Troels Arvin <tr****@arvin.dk>
http://troels.arvin.dk/
Aug 13 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Ulrich Sprick | last post by:
Hi all, (DB2 V7.1 for WinNT) I am looking for a way to determine the free space in my tablespace (containers), but I can't find out. The tablespace in question is a system managed tablespace in...
6
by: Raj | last post by:
How can we do an online restore of a tablespace using the incremental backup's? we are on a partitioned database... Also, how could we use backup copy made by the load (using the copy to option...
1
by: Raj | last post by:
How can we restore a tablespace from a Full backup to a new tablespace with out effecting the existing tablespace.. (can we use the redirect option in the backup command for tablespace recovery...
4
by: Joachim Klassen | last post by:
DB2 V8.2 FP10 on Windows I tested the following HADR scenario: - a new tablespace on a new filesytem is created on the primary System - the replay on standby fails because of lacking permissions...
3
by: hikums | last post by:
Source/Production system: AIX 5.2, DB2 8.2 I have a full backup of sample db taken on 05/01/2006(logs included in backups) I have a tablespace backup of userspace1 taken on 05/03/2006(logs...
8
by: Raj | last post by:
-Is it possible to recover a tablespace just from a full offline backup and offline incremental backup? If not possible -Can we recover tablespace from Full backup , Wednesdays incremental backup...
1
by: airmax | last post by:
Hello DBA’s I have a problem with my TEMP tablespace. It’s 1 GB and its full. Shouldn’t be empty? When the tablespace is full i have to restart the database in order clean up the tablespace, but...
0
by: Lew | last post by:
Hi, Here's the situation. We weekly take a full prod db backup (db has 5 partitions, catalog plus 4 others) and restore it to our dr machine. Then we nightly apply delta backup from our prod...
3
by: Justin | last post by:
What is the difference between automatic and DMS Auto-resize tablespace?
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...
1
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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

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.