Unable to allocate new pages in table space "XXXX" ... but it's 250 megs and I'm only running DDL

Question

Hello,

I'm a DB2 newbie, so I'd appreciate even any pointers on where to start looking. We have great DB2 admins but they're swamped with other issues now, so I'm trying to do some troubleshooting on a development database.

My situation is that I have a tablespace that's giving me this error message

Unable to allocate new pages in table space "[MyTableSpace]".

However, all I'm doing is running multiple (hundreds) of DDL statements, mainly creating tables but also indexes and pk scripts. So, considering that the tablespace has about 250 mg, I shouldn't be running out of space, right?

Here's another thing - it appears that after I leave my script for a while, something "resets" and works for a while, then I begin to have the tablespace issue again. There may be something else that's going on, though.

thanks, Sylvia

Answer 1

Check the extent size for your tablespace (look at the EXTENTSIZE column in SYSCAT.TABLESPACES).

When you create a table, DB2 will allocate a minimum of 2 extents for each table. If your tablespace uses a large extent size (32 pages, for example), it's very easy to blow through 250 Mb.

For example, with a 16kb page size and extent size of 32 pages, creating a table (with no data) will require 1 Mb (16kb * 32 pages/extent * 2 extents) of space.

If you have small tablespaces for testing purposes, make sure your DBA makes the extent size small (say 2 or 4 pages) for the tablespace to avoid this problem.