This post should be a quick one. In older versions of DB2, we had to worry a lot more about tablespace states. Within my career, I can remember that a LOAD operation would put a whole tablespace into “LOAD PENDING”, and if that LOAD operation failed in certain ways, the tablespace would get stuck in that state and we’d have to get it out. I think that went away with DB2 7, even
That’s not a problem any more since loads only affect things at the table level and not at the tablespace level, but it’s still important to know how to query tablespace states. I still fall back to good ol’ LIST TABLESPACES, even though it has been deprecated for a while now. Recently, I issued a command to lower the high water mark for a tablespace and on a LIST TABLESPACES, saw (in part) this:
Tablespace ID = 2 Name = USERSPACE1 Type = Database managed space Contents = All permanent data. Large table space. State = 0x80000 Detailed explanation:
Darn it, it’s showing a non-zero state, but the “Detailed explanation” is not telling me the human-readable form of that state. So to get the details of what that means, you can use:
$ db2tbst 0x80000 State = Move in Progress
This can also be useful if the following error message is received:
SQL0290N Table space access is not allowed. SQLSTATE=55039
If you don’t want to use the antiquated way of looking at tablespaces that I do, you could instead use:
$ db2 "select substr(tbsp_name,1,30) as tbsp_name, substr(TBSP_STATE,1,18) as TBSP_STATE from TABLE(MON_GET_TABLESPACE('',-2)) with ur"
TBSP_NAME TBSP_STATE
------------------------------ ------------------
SYSCATSPACE NORMAL
TEMPSPACE1 NORMAL
USERSPACE1 NORMAL
TAB8K NORMAL
TAB16K NORMAL
TEMPSYS8K NORMAL
TEMPSYS16K NORMAL
TEMPSYS32K NORMAL
USERTEMP32K NORMAL
DBA32K NORMAL
SYSTOOLSPACE NORMAL
11 record(s) selected.
Notice, that returns the human-readable tablespace state without having to use db2tbst.
Many, but not all tablespace states are described in the info center, if you need to know what a particular state actually means: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0060111.html
