--如何能监控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