附录 常用SQL语句 Dynamic SQL

平均执行时间

db2 "select substr(stmt_text,1,30) text,decimal(num_executions,15,0) num_exec,decimal(total_exec_time,15,0) exec_time_s,decimal(1.0*total_exec_time/(num_executions*1.0++0.000001),15,6) aver_time_s,decimal(total_exec_time*100.0/(b.amount),4,2) per1,decimal(rows_read,15,0) rows_read,decimal(rows_read*100.0/(c.amount+1.0),4,2) per,decimal(rows_written,15,0) rows_written,decimal(stmt_sorts,15,0) sort,decimal(sort_overflows,15,0) s_overflow/*,decimal(total_sort_time,15,0) total_sort_time,total_exec_time_ms exec_time_ms,substr(char(total_usr_cpu_time),1,9) usr_cpu_time,substr(char(total_usr_cpu_time_ms),1,9) usr_cpu_time_ms,substr(char(total_sys_cpu_time),1,9) sys_cpu_time,substr(char(total_sys_cpu_time_ms),1,9) sys_cpu_time_ms,stats_fabricate_time,sync_runstats_time*/ from SYSIBMADM.SNAPDYN_SQL a,(select sum(total_exec_time) amount from SYSIBMADM.SNAPDYN_SQL) b,(select sum(rows_read) amount from SYSIBMADM.SNAPDYN_SQL) c order by 2 desc"|more

CPU时间

"db2 ""SELECT  /*SUBSTR(a.DB_NAME,1,3)  AS  DB,*/substr(tpmon_client_userid,1,10) userid,substr(tpmon_client_app,7,3) app,substr(tpmon_acc_str,1,23) str, substr(execution_id,1,7) exec_id, substr(primary_auth_id,1,7) auth_id,substr(char(a.AGENT_ID),1,7) agentid/*,substr(char(coord_agent_pid),1,7) ageneduid*/,substr(char(client_pid),1,7) clienpid, substr(APPL_STATUS,1,8) applstauts ,/*substr(char(timestampdiff(2,char(timestamp(T.snapshot_timestamp)-timestamp(status_change_time)))),1,7) stime_s,*/substr(char(timestampdiff(2,char(t.snapshot_timestamp-timestamp(stmt_start)))),1,7) stmt_time,case when stmt_stop is null then 'N' ELSE 'Y' END STOP,substr(STMT_OPERATION,1,10) operation,SUBSTR(STMT_TEXT,1,10)  AS  STMT_TEXT,substr(char(stmt_usr_cpu_time_s),1,5) ucpu,substr(char(stmt_sys_cpu_time_s),1,4) scpu/*,SUBSTR(APPL_NAME,1,5)  AS  APPNAM,substr(char(NUM_ASSOC_AGENTS),1,5) numagent,substr(char(codepage_id),1,4) cpid,client_platform,is_system_appl*/ FROM  sysibmadm.snapAPPL_INFO a, TABLE(SNAP_GET_STMT('',-1))  AS  T where a.agent_id = t.agent_id and trim(appl_status) not in('UOWWAIT','CONNECTED') order by APPLSTAUTS,AGENTID,timestampdiff(2,char(t.snapshot_timestamp-timestamp(stmt_start))) desc""|more

db2 ""select SECTION_TYPE,EXECUTABLE_ID,NUM_COORD_EXEC,NUM_COORD_EXEC_WITH_METRICS,TOTAL_STMT_EXEC_TIME,AVG_STMT_EXEC_TIME,TOTAL_CPU_TIME,AVG_CPU_TIME,TOTAL_LOCK_WAIT_TIME,AVG_LOCK_WAIT_TIME,TOTAL_IO_WAIT_TIME,AVG_IO_WAIT_TIME,PREP_TIME,ROWS_READ_PER_ROWS_RETURNED,substr(STMT_TEXT,1,1000) STMT_TEXT from SYSIBMADM.MON_PKG_CACHE_SUMMARY order by AVG_CPU_TIME desc fetch first 50 rows only""

db2 -x "" select AVG_CPU_TIME,STMT_TEXT from SYSIBMADM.MON_PKG_CACHE_SUMMARY order by AVG_CPU_TIME desc fetch first 50 rows only""

db2 ""select substr(stmt_text,1,150) text,decimal(char(total_usr_cpu_time),6,0) usr_cpu_time,substr(char(total_sys_cpu_time),1,5) sys_cpu_time,num_executions,total_exec_time,decimal(1.0*total_exec_time/(num_executions*1.0+0.000001),15,6) aver_time_s from SYSIBMADM.SNAPDYN_SQL  order by 2 desc,6 desc""|more

db2 ""SELECT MEMBER,SECTION_TYPE ,PREP_TIME, TOTAL_CPU_TIME/NUM_EXEC_WITH_METRICS as  AVG_CPU_TIME,EXECUTABLE_ID FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T WHERE T.NUM_EXEC_WITH_METRICS <> 0 ORDER BY AVG_CPU_TIME""
V10.5
db2 ""SELECT MEMBER,SECTION_TYPE,PREP_TIME,PREP_WARNING, TOTAL_CPU_TIME/NUM_EXEC_WITH_METRICS as  AVG_CPU_TIME,EXECUTABLE_ID FROM TABLE(MON_GET_PKG_CACHE_STMT ( 'D', NULL, NULL, -2)) as T WHERE T.NUM_EXEC_WITH_METRICS <> 0 ORDER BY AVG_CPU_TIME"""

Long running

db2 "select substr(AGENT_ID,1,5) agent_id,substr(APPL_NAME,1,15) app_name,substr(AUTHID,1,10) authid,substr(INBOUND_COMM_ADDRESS,1,20) ip,substr(ELAPSED_TIME_MIN,1,10) exec_time_min,substr(STMT_TEXT,1,30) stmt from SYSIBMADM.LONG_RUNNING_SQL where ELAPSED_TIME_MIN is not null and STMT_TEXT is not null order by ELAPSED_TIME_MIN desc"|more