Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 1 down vote accepted

Try the monitoring function:

select 
  tbsp_name, 
  case TBSP_USING_AUTO_STORAGE
    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 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 at 18:26
add comment

Your Answer

 
discard

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.