Unable to allocate new pages in table space

[Ratan Babu N]

Hi,
When i tried to load from a dataset to a Db2 table by using Db2 enterprise stage.
Initially i used an upsert mode with a never satisfying update condition and always inserting records into the table. But most of the records are rejected with out any warnings.

then i used write method option, but the job is aborted with the following message.

Error Idx = 12;
DB2 Driver Embedded SQL message: SQL0289N Unable to allocate new pages in table space "TEDDAT902".
SQLSTATE=57011
;
sqlcode = -289;
sqlstate = 57011
Execute failed


when i tried to manually insert into the table, i got the following message

DB2 SQL error: SQLCODE: -289, SQLSTATE: 57011, SQLERRMC: TEDDAT902
Message: Unable to allocate new pages in table space "TEDDAT902".

is this issue related to database or not.

Plz help me in this issue

[ray.wurlod]

It's in the database, not in DataStage. Have your DBA explain what it means. Basically the table space in which the table was created has been set up with a certain maximum size, and the table has hit that limit. The DBA can allocated more pages in the table space, but will probably ask you to justify your need for more space.

[bcarlson]

Like Ray said, you ran out of space in your tablespace. Now, that brings up an interesting topic - is there a way to avoid this?

You can always monitor tablespace capacity and do incremental increases whenever a space reaches a certain level of usage - for example, add 10% to a tablespace when it reaches 80% capacity (if the space is 10GB and it has 8GB full, then add 1 GB). The problem with this is that it does not guarantee you won't fill up the space (what if your table jumps 20% in volume?) and you end up with 'wasted' space; that is, you have more space allocated than is actually being used.

Now, depending on the growth requirements of your table there are ways to setup the tablespace to allow automatic allocation of space. There are 2 different kinds of tablespace - SMS (system managed space) and DMS (database managed space). You can check with your DBA or your DB2 documentation on the what the specifics are. I know these exist for DB2 UDB, not sure about the non-enterprise DB2.

At least as of DB2 8.2, both tablespace types can be setup to grow automatically so that jobs don't fail (and you don't have to page your DBA in the middle of the night). SMS has been able to do it since v7. I think as of one of the v8 releases, DMS now has a similar capability.

So, check with your DBA. There may be some good options out there to avoid these kinds of failures.

Hope this helps,

Brad.