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:

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home

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.