DB2如何获取完整的SQL语句

newcomer1981

我用如下语句获取到一些很耗时的语句，但是stmt_text是截断的，请问你我如何获取到这些语句的完成语句？
select total_exec_time,num_executions,total_exec_time/num_executions time_per_exec,stmt_text from table(snapshot_dyn_sql('',-1)) A
where num_executions>0
order by total_exec_time/num_executions desc
fetch first 20 rows only;

谢谢

sdusun

原帖由 newcomer1981 于 2010-9-9 11:21 发表
我用如下语句获取到一些很耗时的语句，但是stmt_text是截断的，请问你我如何获取到这些语句的完成语句？
select total_exec_time,num_executions,total_exec_time/num_executions time_per_exec,stmt_text from table(snapshot_dyn_sql('',-1)) A
where num_executions>0
order by total_exec_time/num_executions desc
fetch first 20 rows only;

谢谢

怎么会呢？！
STMT_TEXT CLOB(16M)1 stmt_text - SQL statement text  
这个字段是10MB大小的CLOB字段，还放不下你的SQL？ please double check

cngd1

会不会用的查询客户端，例如Quest Central会限制返回字段的宽度只有50个字节，超过的都会被截断

wy3552128

1. 
   
2. #监控执行成本最高的sql语句
   
3. select agent_id,rows_selected,rows_read from sysibmadm.snapappl fetch first 10 rows only;
   
4. 
   
5. #监控运行最长的sql语句
   
6. select substr(appl_name,1,15) as Appl_name,elapsed_time_min as "Elapsed Min.",appl_status as "Status ",substr(authid,1,10) as auth_id,
   
7. substr(inbound_comm_address,1,15) as "IP Address",substr(stmt_text,1,30) as "SQL Statement"
   
8. from sysibmadm.long_running_sql order by 2 desc;
   
9. 
   
10. select * from sysibmadm.long_running_sql order by 2 desc;
    
11. 
    
12. #监控sql准备和预编译时间最长的sql语句
    
13. average_execution_time_s sql语句平均执行时间
    
14. prep_time_ms   最长的sql预编译时间
    
15. prep_time_precent 预编译时间占整个执行时间的百分比
    
16. select num_executions,average_execution_time_s,prep_time_ms,prep_time_precent,substr(stmt_text,1,40) as "SQL_Text"
    
17. from sysibmadm.query_prep_cost where average_execution_time_s>0 order by prep_time_precent desc;
    
18. 
    
19. #监控执行次数最多的sql语句
    
20. Select num_executions "Num Execs",average_execution_time_s as "Avg Time(sec)",stmt_sorts as "Num Sorts",sorts_per_execution as "Sorts Per Stmt",
    
21. substr(stmt_text,1,35) as "SQL Stmt" from sysibmadm.top_dynamic_sql where num_executions>0 order by 1 desc fetch first 5 rows only;
    
22. 
    
23. Select num_executions "Num Execs",average_execution_time_s as "Avg Time(sec)",stmt_sorts as "Num Sorts",sorts_per_execution as "Sorts Per Stmt",
    
24. substr(stmt_text,1,35) as "SQL Stmt" from sysibmadm.top_dynamic_sql where num_executions>0 order by 2 desc fetch first 5 rows only;
    
25. 
    
26. 
    
27. #监控排序次数最多的sql语句
    
28. select stmt_sorts,sorts_per_execution,substr(stmt_text,1,60) as stmt_text from top_dynamic_sql order by stmt_sorts fetch first 5 rows only;
    

复制代码

张广舟

用表函数mon_get_pkg_cache_stmt('D', NULL, NULL, -2)试试。 e.g.
db2 "select * from table(mon_get_pkg_cache_stmt('D', NULL, NULL, -2)) as T order by total_cpu_time fetch first 10 rows only"
（查看消耗CPU时间最多的10个静态SQL语句。将参数‘D’改为‘S’，可以得到对应动态SQL语句的结果，改为NULL则得到对应所有SQL语句的结果）