db2数据库操作.docVIP

检查表空间使用率1select char(TBSP_NAME,50) as TBSP_NAME, TBSP_USABLE_PAGES, TBSP_USED_PAGES, rtrim(100*TBSP_USED_PAGES/TBSP_USABLE_PAGES)||% as USED_RATE from sysibmadm.SNAPTBSP_PART where TBSP_USABLE_PAGES0 order by TBSP_NAME,DBPARTITIONNUM测试结果：INCLUDEPICTURE \d /attachment/201505/41430726902RH8q.png \* MERGEFORMATINET 检查表空间使用率2SELECT TOTAL_PAGES, USED_PAGES, FREE_PAGES, rtrim(char(USED_PAGES*100/TOTAL_PAGES))||% as USED_RATE, rtrim(char(FREE_PAGES*100/TOTAL_PAGES))||% as Free_RATE FROM table (SNAPSHOT_TBS_CFG(sample, -1)) t实验结果2：INCLUDEPICTURE \d /attachment/201505/414307271018Pmi.png \* MERGEFORMATINET 检查使用率3SELECT substr(tbsp_name,1,20) as TABLESPACE_NAME, substr(tbsp_content_type,1,10) as TABLESPACE_TYPE, sum(tbsp_total_size_kb)/1024 as TOTAL_MB, sum(tbsp_used_size_kb)/1024 as USED_MB, sum(tbsp_free_size_kb)/1024 as FREE_MB, tbsp_page_size AS PAGE_SIZE, TBSP_UTILIZATION_PERCENT as percent FROM SYSIBMADM.TBSP_UTILIZATION GROUP BY tbsp_name, tbsp_content_type, tbsp_page_size, TBSP_UTILIZATION_PERCENT ORDER BY TBSP_UTILIZATION_PERCENT; Sql语句使用内存的情况 SELECT??creation_time?? ? ????????,last_execution_time? ? ????????,total_physical_reads ? ????????,total_logical_reads?? ? ????????,total_logical_writes ? ????????,?execution_count? ? ????????,?total_worker_time ? ????????,?total_elapsed_time? ? ????????,?total_elapsed_time?/?execution_count?avg_elapsed_time ? ????????,SUBSTRING(st.text,?(qs.statement_start_offset/2)?+?1, ? ?????????((CASE?statement_end_offset? ? ??????????WHEN?-1?THEN?DATALENGTH(st.text) ? ??????????ELSE?qs.statement_end_offset?END? ? ????????????-?qs.statement_start_offset)/2)?+?1)?AS?statement_text ? FROM?sys.dm_exec_query_stats?AS?qs ? CROSS?APPLY?sys.dm_exec_sql_text(qs.sql_handle)?st ? ORDER?BY?total_elapsed_time?/?execution_count?DESC; 缓冲池操作 查看缓冲池大小：SELECT BPNAME,NPAGES,PAGESIZE FROM SYSCAT.BUFFERPOOLS修改缓冲池大小：ALTER BUFFERPOOL bp_name SIZE number_of_pages创建缓冲池时指定大小：CREATE BUFFERPOOL bp_name SIZE n