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?


Try the monitoring function:

    when 1 then 'YES'
    else 'NO'
  end auto_storage
from table (sysproc.MON_GET_TABLESPACE('',-1))
  • Awesome as always mustaccio! :) Oct 9 '13 at 20:32
  • 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. 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. Apr 4 '14 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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