Determining the Tablespace Type

db2 get snapshot for tablespaces on

　
To check information about the automatic extend and automatic storage status of all tablespaces, you can also use the following SQL statement:

db2 "SELECT SUBSTR(TBSP_NAME,1,10) AS TBSP_NAME, TBSP_TYPE, TBSP_CONTENT_TYPE, TBSP_USING_AUTO_STORAGE, TBSP_AUTO_RESIZE_ENABLED FROM TABLE(SNAP_GET_TBSP_V91('')) AS T"

A value of 1 in the columns TBSP_USING_AUTO_STORAGE and TBSP_AUTO_RESIZE_ENABLED indicates that the respective feature is enabled, 0 would indicate that it is not enabled.

　
To check if the database is enabled for automatic storage, use the following command:
db2 "get snapshot for database on "

　
　
　
　
　
　
　
To check if the tablespaces in your DB2 9.7 installation have the reclaimable storage attribute, use the following SQL statement:

db2 "SELECT varchar(tbsp_name, 30) as tbsp_name, tbsp_type, reclaimable_space_enabled FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t"

A value of 1 in the reclaimable_space_enabled column would indicate that this tablespace has the reclaimable storage attribute.