附录 常用SQL语句 Dynamic SQL

db2 "SELEct substr(stmt_text,1,30) text,decimal(num_EXECUTIONS,15,0) num_exec,decimal(@R_446_10586@l_exec_time,15,0) exec_time_s,decimal(1.0*@R_446_10586@l_exec_time/(num_EXECUTIONS*1.0++0.000001),15,6) aver_time_s,decimal(@R_446_10586@l_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(@R_446_10586@l_sort_time,15,0) @R_446_10586@l_sort_time,@R_446_10586@l_exec_time_ms exec_time_ms,substr(char(@R_446_10586@l_usr_cpu_timE),1,9) usr_cpu_time,substr(char(@R_446_10586@l_usr_cpu_time_ms),1,9) usr_cpu_time_ms,substr(char(@R_446_10586@l_sys_cpu_timE),1,9) sys_cpu_time,substr(char(@R_446_10586@l_sys_cpu_time_ms),1,9) sys_cpu_time_ms,stats_fabricate_time,sync_runstats_time*/ from SYSIBMADm.SNAPDYN_SQL a,(SELEct sum(@R_446_10586@l_exec_timE) amount from SYSIBMADm.SNAPDYN_SQL) b,(SELEct sum(rows_read) amount from SYSIBMADm.SNAPDYN_SQL) c order by 2 desc"|more

"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,@R_446_10586@L_STMT_EXEC_TIME,AVG_STMT_EXEC_TIME,@R_446_10586@L_CPU_TIME,AVG_CPU_TIME,@R_446_10586@L_LOCK_WAIT_TIME,AVG_LOCK_WAIT_TIME,@R_446_10586@L_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(@R_446_10586@l_usr_cpu_timE),6,0) usr_cpu_time,substr(char(@R_446_10586@l_sys_cpu_timE),1,5) sys_cpu_time,num_EXECUTIONS,@R_446_10586@l_exec_time,decimal(1.0*@R_446_10586@l_exec_time/(num_EXECUTIONS*1.0+0.000001),15,6) aver_time_s from SYSIBMADm.SNAPDYN_SQL  order by 2 desc,6 desc""|@H_496_28@more

db2 ""SELECT MEMBER,SECTION_TYPE ,PREP_TIME, @R_446_10586@L_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, @R_446_10586@L_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"""

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