DB2 Tablespace and Bufferpool Commands

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"