Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

First off, we are on both Windows and AIX and are using DB2 ESE 9.7 FP 4.

I am trying to determine dynamically whether or not table spaces are set up via automatic storage or not (so we can dynamically script how to lower the high water mark when necessary). I can see whether they are defined SMS or DMS "under the covers" via statements like db2 list tablespaces or db2 list tablespaces show detail.

I have also issued queries against the system catalog tables and views (ie, select * from syscat.tablespaces and select * from sysibm.systablespaces).

All I can tell is that the tablespace is defined as SMS or DMS. How can I tell whether or not the tablespace is configured for automatic storage? I know there must be a way I can do it dynamically, because if I use IBM Data Studio and visit the tablespaces "view" and click on properties, I can see whether or not the table space is automatic storage or not. I am assuming there must be a configuration or a query behind that.

Just wondering, how do we figure this out?

share|improve this question
up vote 2 down vote accepted

Try the monitoring function:

    when 1 then 'YES'
    else 'NO'
  end auto_storage
from table (sysproc.MON_GET_TABLESPACE('',-1))
share|improve this answer
Awesome as always mustaccio! :) – Chris Aldrich Oct 9 '13 at 20:32
Thanks. These are easy problems... – mustaccio Oct 9 '13 at 23:58
Wanted to add here, that in addition to mustaccio's answer, I discovered I can use db2pd -d <dbname> -tablespaces. There is a column 'AS' that if marked Y, shows the table is automatic storage. If found that on the db2commerce blog. – Chris Aldrich Apr 4 '14 at 17:58
FYI that what mustaccio found will only work on 9.7 FP 5 or higher. We are 9.7 FP 4, so I have to use what I found (the db2pd) But what mustaccio has recommended would be the correct answer. So leaving it. – Chris Aldrich Apr 4 '14 at 18:26

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.