1.Get the TOP 10 SQLS by the Total Execution time

db2 "SELECT substr(stmt_text,1,100) as sql_statement, total_exec_time, total_sys_cpu_time, total_usr_cpu_time,num_executions, num_compilations FROM sysibmadm.snapdyn_sql ORDER BY total_exec_time desc FETCH FIRST 10 ROWS ONLY"

2.Get the Top 10 SQLS by Average Execution Timehttp://www.cndba.cn/hbhe0316/article/107906

db2 "SELECT substr(stmt_text,1,100) as sql_statement, total_exec_time, num_executions,DECIMAL((real(total_exec_time) / real(num_executions)),18,9) as avg_exec_time FROM sysibmadm.snapdyn_sql WHERE num_executions > 0 ORDER BY 4 desc FETCH FIRST 10 ROWS ONLY"

3.Get the Top 10 SQLS by Average CPU Time

db2 "SELECT substr(stmt_text,1,100) as sql_statement, total_sys_cpu_time, total_usr_cpu_time, num_executions,DECIMAL(((real(total_sys_cpu_time) + real(total_usr_cpu_time)) / real(num_executions)),18,9) as avg_cpu_time FROM sysibmadm.snapdyn_sql WHERE num_executions > 0 ORDER BY avg_cpu_time desc FETCH FIRST 10 ROWS ONLY"

4.Top 10 Ranking SQL by Number of Executionhttp://www.cndba.cn/hbhe0316/article/107906

db2 "SELECT substr(stmt_text,1,100) as sql_statement, total_exec_time, num_executions, stmt_sorts,num_compilations, DECIMAL((real(total_exec_time) / real(num_executions)),18,9) as avg_exec_time FROM sysibmadm.snapdyn_sql WHERE num_executions > 0 ORDER BY 3 desc FETCH FIRST 10 ROWS ONLY"

5.Top 10 Ranking SQL by Number of sorts

db2 "SELECT substr(stmt_text,1,100) as sql_statement, total_exec_time, num_executions, stmt_sorts,num_compilations, DECIMAL((real(total_exec_time) / real(num_executions)),18,9) as avg_exec_time FROM sysibmadm.snapdyn_sql WHERE num_executions > 0 ORDER BY stmt_sorts desc FETCH FIRST 10 ROWS ONLY"

6.TOP 10 SQLS BY IO

db2 "select substr(stmt_text,1,100) as sql_statement, stmt_exec_time, num_executions,(pool_read_time + pool_write_time+direct_read_time + direct_write_time) as io_time from table(mon_get_pkg_cache_stmt(null,null,null,-2)) as t order by io_time desc fetch first 10 rows only"

7.监控视图APPLICATIONS ，检索应用连接显示执行状态、时长及SQLhttp://www.cndba.cn/hbhe0316/article/107906http://www.cndba.cn/hbhe0316/article/107906

db2 "select substr(STMT_TEXT,1,50) as sql_statement,APPLICATION_HANDLE, substr(APPLICATION_NAME,1,10) as APPLICATION_NAME,ELAPSED_TIME_SEC, ACTIVITY_STATE, QUERY_COST_ESTIMATE from sysibmadm.mon_current_sql order by ELAPSED_TIME_SEC fetch first 10 rows only"

8.监控视图APPLICATIONS ，检索应用连接的状态、地址等信息http://www.cndba.cn/hbhe0316/article/107906http://www.cndba.cn/hbhe0316/article/107906http://www.cndba.cn/hbhe0316/article/107906

db2 "select substr(A.AUTHID,1,10) as AUTH_ID, substr(A.APPL_NAME,1,20) as app_name, A.AGENT_ID as APP_HANDLE, substr(A.APPL_ID,1,25) as APPLICATION_ID, substr(A.APPL_STATUS,1,15) as status, A.STATUS_CHANGE_TIME, second(current timestamp-A.STATUS_CHANGE_TIME) as SEC_IN_STATUS, substr(A.CLIENT_NNAME,1,15) as CLIENT_NNAME from SYSIBMADM.APPLICATIONS A with ur"

版权声明：本文为博主原创文章，未经博主允许不得转载。

DB2