db2 常見監控

--如何能監控DB2 資料庫日誌的利用率

SELECT LOG_UTILIZATION_PERCENT FROM SYSIBMADM.LOG_UTILIZATION AS LOG_UTILIZATION

--檢視執行時間最長的 5 個動態 SQL 語句

select AVERAGE_EXECUTION_TIME_S , SUBSTR(STMT_TEXT,1,200) AS STMT_TEXT from SYSIBMADM.TOP_DYNAMIC_SQL order by AVERAGE_EXECUTION_TIME_S desc fetch first 5 rows only;

--檢視執行頻率最高的 5 個動態 SQL 語句：

select NUM_EXECUTIONS, AVERAGE_EXECUTION_TIME_S, STMT_SORTS, SORTS_PER_EXECUTION, SUBSTR(STMT_TEXT,1,200) AS STMT_TEXT from SYSIBMADM.TOP_DYNAMIC_SQL ORDER BY NUM_EXECUTIONS desc fetch first 5 rows only;

--檢視排序次數最多的 5 個動態 SQL 語句：

select STMT_SORTS, SORTS_PER_EXECUTION, substr(STMT_TEXT,1,200) as STMT_TEXT from SYSIBMADM.TOP_DYNAMIC_SQL order by STMT_SORTS desc fetch first 5 rows only;

--v8檢視執行時間最長的 5 個動態 SQL 語句：

select TOTAL_EXEC_TIME/NUM_EXECUTIONS, SUBSTR(STMT_TEXT,1,200) AS STMT_TEXT FROM TABLE( SNAPSHOT_DYN_SQL (CAST(NULL AS VARCHAR(1)), CAST (NULL AS INTEGER))) as SNAPSHOT_DYN_SQL order by TOTAL_EXEC_TIME/NUM_EXECUTIONS desc fetch first 5 rows only;

--v8檢視執行頻率最高的 5 個動態 SQL 語句：

select NUM_EXECUTIONS, TOTAL_EXEC_TIME/NUM_EXECUTIONS, STMT_SORTS, STMT_SORTS/NUM_EXECUTIONS as SORTS_PER_EXECUTION, SUBSTR(STMT_TEXT,1,200) AS STMT_TEXT from TABLE( SNAPSHOT_DYN_SQL (CAST(NULL AS VARCHAR(1)), CAST (NULL AS INTEGER))) as SNAPSHOT_DYN_SQL ORDER BY NUM_EXECUTIONS desc fetch first 5 rows only;

--v8檢視排序次數最多的 5 個動態 SQL 語句：

select STMT_SORTS, STMT_SORTS/NUM_EXECUTIONS as SORTS_PER_EXECUTION, substr(STMT_TEXT,1,200) as STMT_TEXT from TABLE( SNAPSHOT_DYN_SQL (CAST(NULL AS VARCHAR(1)), CAST (NULL AS INTEGER))) as SNAPSHOT_DYN_SQL order by STMT_SORTS desc fetch first 5 rows only;

--DB2PD程式快速定位鎖定SQL語句

db2pd -db sample -locks -transactions –applications -dynamic -file locklog

[@more@]