DB2 Tablespace and Bufferpool Commands



View the names, pagesize, and number of pages of the bufferpools in your database:

=>db2 "select bpname,pagesize,npages from syscat.bufferpools"

Make a bufferpool resize automatically.  You must have STMM turned on at the database configuration level

=>db2 alter bufferpool bp1 size AUTOMATIC 

Alter a bufferpool size that is a fixed size

=>db2 ALTER BUFFERPOOL IBMDEFAULTBP SIZE 15000;

Add space to a DMS tablespace:

=>db2 "alter tablespace <tblspace> extend (all 8000)"

 
###What is in the bufferpools
db2pd -db dbname -pages

 

### tablespace states
db2 "select substr(tbsp_name,1,25)as NAME, tbsp_type,substr(tbsp_state,1,25)as STATE, tbsp_auto_resize_enabled,tbsp_utilization_percent, dbpartitionnum from sysibmadm.tbsp_utilization order by dbpartitionnum"

 ###Tablespace usage
db2 "select substr(tbsp_name,1,25)as NAME, tbsp_type,substr(tbsp_state,1,25)as STATE,tbsp_utilization_percent, tbsp_total_pages,tbsp_used_pages,dbpartitionnum from sysibmadm.tbsp_utilization where tbsp_name like '%ENTER_SOME_NAME%' order by dbpartitionnum"
db2 "select substr(tbsp_name,1,15),tbsp_id,TBSP_PENDING_FREE_PAGES, tbsp_max_page_top,tbsp_total_pages,tbsp_used_pages,dbpartitionnum FROM TABLE(MON_GET_TABLESPACE('',-2)) AS t where tbsp_name in (ENTER_SOME_NAME)"

db2 "select substr(tbsp_name,1,25)as NAME, tbsp_type,substr(tbsp_state,1,25)as STATE,tbsp_utilization_percent, tbsp_total_pages,tbsp_used_pages,dbpartitionnum from sysibmadm.tbsp_utilization where tbsp_name like '%TMP%' order by tbsp_name,dbpartitionnum"
 
###Reclaim pages from an Automatic Storage tablespace
alter tablespace TBSP_NAME reduce max;
commit;
 
#### RECLAIM STORAGE
db2 "alter tablespace TBSP_NAME reduce 25 percent"
db2 "select substr(tbsp_name,1,25)as NAME, substr(tbsp_state,1,20),tbsp_free_pages,tbsp_used_pages, tbsp_auto_resize_enabled,tbsp_utilization_percent, dbpartitionnum from sysibmadm.tbsp_utilization where tbsp_name = 'TBSP_NAME' and tbsp_state not like '%NORMAL%' order by dbpartitionnum"
db2 "alter tablespace 'TBSP_NAME' reduce stop"