Hi,
I am new to the DB2 environment. We ran into a situation where a bunch Tablespaces went into backup pending state while using the database. We had to take a backup of those tablespaces to have the database up and running. My question is Under what circumstances does DB2 keep the tablespaces in 'Backup Pending' state. I would appreciate an explanation of the scenarios if possible so that I can make sure it doesn't happen in our environment.

Thanks

- Raj
rajosi_us@hotmail.com

I think LOAD will put a tablespace in the "backup pending" state unless you specify NONRECOVERABLE or COPY YES.

Thanks for the response. Does DB2 put the tablespaces in Backup Pending state even though the LOAD is successful? or does it put them in Backup Pending only when the LOAD is a failure..

- Raj

Hi.

Whenever a load is successful it would put the tablespace in a backup pending state. Because load inserts data by pages and since you would be in recovery mode you have to do a backup to ensure consitency and integrity.

If the load fails, the in order to access the tablespace/table you have to do a load terminate on the table and the tablespace is kept in the backup pending mode.

You can test the same and verify it on a test db.

This command will list all tablesapce in pending state:

SELECT SNAPSHOT_TIMESTAMP,TBSP_NAME,DBPARTITIONNUM,TBSP_I D,TBSP_STATE,TBSP_PREFETCH_SIZE,TBSP_NUM_QUIESCERS ,TBSP_STATE_CHANGE_OBJECT_ID,TBSP_STATE_CHANGE_TBS P_ID,TBSP_MIN_RECOVERY_TIME,TBSP_TOTAL_PAGES,TBSP_ USABLE_PAGES,TBSP_USED_PAGES,TBSP_FREE_PAGES,TBSP_ PENDING_FREE_PAGES,TBSP_PAGE_TOP,REBALANCER_MODE,R EBALANCER_EXTENTS_REMAINING,REBALANCER_EXTENTS_PRO CESSED,REBALANCER_PRIORITY,REBALANCER_START_TIME,R EBALANCER_RESTART_TIME,REBALANCER_LAST_EXTENT_MOVE D,TBSP_NUM_RANGES,TBSP_NUM_CONTAINERS,TBSP_INITIAL _SIZE,TBSP_CURRENT_SIZE,TBSP_MAX_SIZE,TBSP_INCREAS E_SIZE,TBSP_INCREASE_SIZE_PERCENT,TBSP_LAST_RESIZE _TIME,TBSP_LAST_RESIZE_FAILED
FROM "SYSIBMADM"."SNAPTBSP_PART"
where TBSP_STATE<>'NORMAL' order by TBSP_NAME, DBPARTITIONNUM

to fix Tablespace Backup Pending State, execute this query:
db2 "backup db bcudb on dbpartitionnums(0,1,2,3,4,5,6) tablespace (USERSPACE1) online to /dev/null without prompting"