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

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

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.

Could you please tell us what type of your tablespace is? for following situations: 1. tablespace is managed by automatic storage checkout if your filesystem is out of use 2. tablespace is managed by database if you have reach the max_size of the tablespace 3. tablespace is managed by system if your filesystem is used up as situation 1

Thanks!