SQL0289N during a table reorg. DB2 reorg does not use the temporary tablespace specified in USE option of the REORG command.

Cause

Here is an example of a table reorg that has USE option specified in the REORG command.

REORG TABLE <schemaName>.<tableName> INDEX <schemaName>.<indexName> USE tempsys_4k_2

Assume that we have three temporary tablespaces with pagesize of 4KB in our database:

tempsys_4k_1 -> tablespaceID = 20
tempsys_4k_2 -> tablespaceID = 25
tempsys_4k_3 -> tablespaceID = 30

During a table reorg, if a temporary tablespace runs out of space, it may result in one of the following two error messages:

SQL2216N SQL error "-289" occurred while reorganizing a
database table or its indexes.
OR
SQL0289N Unable to allocate new pages in table space
"<tablespace-name>".

In a specific case, the <tablespace-name> in the later error may not be the tablespace specified in the USE option of the REORG command. This may lead users believe that the REORG command is not using the temporary tablespace specified in the USE option. Let's examine why you may see this behavior.

There are two phases a table reorg will go through:
1. Reorging the table data
2. Recreating the index from the organized table data

In the above example, DB2 will use tempsys_4k_2 tablespace in first phase to reorg the table data as specified in the USE option.

During index create phase, a sort will be used to rebuild the index. If there is not enough space in SORTHEAP during the sort operation, the sort may spill to a temporary tablespace. If there is more than one temporary tablespace of pagesize 4k in the database, we may select another temporary tablespace for sort spill than the one specified in the USE option. In this case, it will select the tablespace in round robin fashion starting with the tablespace next in number to the last one used in the reorg operation.

In our example, the last used tablespace would be tempsys_4k_2 during table data reorg phase. So, the tablespace used here for sort spill is the tablespace next in number(tempsys_4k_3 -> tablespaceID = 30) to the last one used (tempsys_4k_2 -> tablespaceID = 25) in the reorg operation. If for some reason if we can't get tempsys_4k_3, we will go after tempsys_4k_1 and so on.

If the reorg runs out of space during table data reorg phase, the error message will display tempsys_4k_2 the for tablespace name. The db2diag.log will show the following entry:

2005-09-30-18.09.03.042830 Instance:<instanceID> Node:012
PID:<pid>(db2agntp) TID: <tid> Appid: <appId>
buffer pool services sqlbAllocateExtent Probe:830 Database:<dbName>

ADM6044E The DMS table space "TEMPSYS_4K_2" (ID "25") is full. Additional space can be added to the table space by either adding new containers or extending existing ones using the ALTER TABLESPACE SQL statement.

If the reorg runs out of space during index create phase, the error message will display tempsys_4k_3 for the tablespace name and the db2diag.log will show the entry below:

2005-09-30-18.09.03.042830 Instance:<instanceID> Node:012
PID:<pid>(db2agntp) TID: <tid> Appid: <appId>
buffer pool services sqlbAllocateExtent Probe:830 Database:<dbName>

ADM6044E The DMS table space "TEMPSYS_4K_3" (ID "30") is full. Additional space can be added to the table space by either adding new containers or extending existing ones using the ALTER TABLESPACE SQL statement.

In summary, users may see a different tablespace name in the error message for SQL0289N and in the db2diag.log if reorg runs out of space. It simply means that a different tablespace than the one specified in the USE option was used for index create phase.