Find and translate a tablespace state

Posted by

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

Lead Db2 Database Engineer and Service Delivery Manager , XTIVIA
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.