DB2 Tablespace in state Restore pending

db2db2-luwtablespaces

I have the problem that in my DB2 database (SLES, Db2 v10.5) my User Temp Tablespaces are all in state Restore pending, Storage must be defined, Storage may be defined.

When I set off the following command db2 list tablespace I get the following output (e.g.):

Tablespace ID                        = 6
Name                                 = USERTEMP4
Type                                 = System managed space
Contents                             = User Temporary data
State                                = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Can anybody help me to escape from this situation? I didn't start any restore recently so I'm wondering how or why the tablespaces have changed to this state.

Best Answer

May be you didn't do a restore, but someone surely did, as the indicated tablespace state is only possible when a redirected restore has been initiated -- this is well described in the manual.

Normally to complete the redirected restore you will need to issue SET TABLESPACE CONTAINERS FOR 6 USING (PATH '/whatever'), where "6" is the tablespace ID, and similarly for all other tablespaces in this state, then run RESTORE DB yourdb CONTINUE to complete the operation (details here).

However, since you apparently don't recognize this restore operation, you'll probably want to abort it instead: RESTORE DB yourdb ABORT.

Related Solutions

DB2 9.5 How to get tablespace monitor information

Use the TBSP_UTILIZATION administrative view - Retrieve table space configuration and utilization information. See the "Essential DB2 Health Check" for a recommendation on how to use it, especially the sections on File System Free Space and DMS Tablespace Free Space.

Example:

select substr(tbsp_name,1,10) "Name", tbsp_utilization_percent "Used%", tbsp_free_pages "FreePages", tbsp_usable_Pages "UsablePages", tbsp_used_pages "UsedPages", tbsp_page_top "HWM", tbsp_auto_resize_Enabled "AutoResize 1=Yes", tbsp_increase_size "Increase Size", tbsp_max_size "MaxSizeBytes -1=ulimited", tbsp_last_resize_failed "ResizeFailed 1=Yes", tbsp_page_size "PgSize Bytes"
from SYSIBMADM.TBSP_UTILIZATION
where tbsp_type = 'DMS';

Specifically, you are after the tbsp_increase_size field from the SYSIBMADM.TBSP_UTILIZATION table, where tbsp_type = 'DMS' as show in the query above. (The query has a few other things that are helpful in it too.)

Db2 – way to determine automatic storage table spaces

Try the monitoring function:

select 
  tbsp_name, 
  case TBSP_USING_AUTO_STORAGE
    when 1 then 'YES'
    else 'NO'
  end auto_storage
from table (sysproc.MON_GET_TABLESPACE('',-1))

Best Answer

Related Solutions

DB2 9.5 How to get tablespace monitor information

Db2 – way to determine automatic storage table spaces

Related Question