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

Tablespace remains full after deletions

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
"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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.