Find and translate a tablespace state

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 Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 544

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.