Db2 – Tablespace error SQLCODE=-289, SQLSTATE=57011

db2db2-luw

We are trying to bulk insert ~200 million records as initial load into a DB2 database.

During the process we get the following error:

SQLCODE=-289, SQLSTATE=57011, SQLERRMC=TEST_IS

Does it mean we ran out of disk space?
How to add additional space?

Please find the following details of the tablespace:

  • it is a Database-managed space.
  • type is large table space,
  • EXTENTSIZE is 32
  • PAGESIZE is 32768

I am not a DBA and not able to understand the error.

Best Answer

This probably means you ran out of space within the DMS tablespace. DMS tablespaces pre-allocate their storage. They can be set to auto-grow when full but perhaps this one isn't, or it's hit the maximum size specified.

For more information on the particular error you can use the "db2 ?" facility. In this case, the error page is particularly verbose (there are many sub-categories) so here's a sample of the output:

$ db2 ? sql0289                                                                                [109/109]


SQL0289N  Unable to allocate new pages in table space
      "<tablespace-name>".

Explanation: 

One of the following conditions is true on one or more database
partitions:

1        

         One of the containers assigned to this SMS table space has
         reached the maximum file size. This is the likely cause of the
         error.


2        

         All the containers assigned to this DMS table space are full.
         This is the likely cause of the error.

[... lots more error codes ...]

User response: 

Perform the action corresponding to the cause of the error:

1        

         Re-create as an AUTOMATIC STORAGE table space or an SMS table
         space with more directories (PATHs) such that: (number of
         directories) >= (max tablesize / maxfilesize) or re-create.
         Note that maximum file size is operating system dependent.
         Re-creating the table space as AUTOMATIC STORAGE allows you use
         storage groups.


2        

         Add new container(s) to the DMS table space and try the
         operation again, after the rebalancer has made the new pages
         available for use.

[... lots more responses ...]

sqlcode: -289

sqlstate: 57011


   Related information:
   ALTER TABLESPACE statement

As it states, the most likely cause is the containers for the tablespace are full, so add some new containers (or extend the existing ones) with ALTER TABLESPACE (there's several examples at the bottom). You will likely need to ask your DBA to do this (as the manual page states, this requires SYSADM or SYSCTRL privilege).

Related Question