DB2中,有些SQL语句返回的行数可能很多,有些SQL语句的执行时间可能很长, 如何把这些SQL语句抓取出来?
下面的例子示意了如何抓取返回行数超过15行的SQL语句。 测试了3条SQL,其中有两条返回结果超过15行
db2 "connect to SAMPLE"
db2 "CREATE EVENT MONITOR WLM_EVENT FOR ACTIVITIES WRITE TO TABLE ACTIVITY (TABLE WLM_EVENT ),ACTIVITYSTMT (TABLE WLM_EVENT_STMT ),ACTIVITYVALS (TABLE WLM_EVENT_VALS ),CONTROL (TABLE WLM_EVENT_CONTROL )"
db2 "CREATE THRESHOLD DBMAXCOST_COLLECT FOR DATABASE ACTIVITIES ENFORCEMENT DATABASE WHEN SQLROWSRETURNED > 15 COLLECT ACTIVITY DATA WITH DETAILS AND VALUES CONTINUE"
db2 "set event monitor WLM_EVENT state=1"
db2 "select * from employee"
db2 "select * from employee where empno < 150"
db2 "select * from employee where empno > 150"
db2 "set event monitor WLM_EVENT state=0"
db2 "select PARTITION_KEY, ACTIVATE_TIMESTAMP,substr(APPL_ID,1,40) as APPL_ID, substr(STMT_TEXT,1,50) as STMT_TEXT from WLM_EVENT_STMT"
PARTITION_KEY ACTIVATE_TIMESTAMP APPL_ID STMT_TEXT
------------- -------------------------- ---------------------------------------- --------------------------------------------------
0 2016-04-27-09.55.14.464777 *LOCAL.DB2INST1.160427014639 select * from employee
0 2016-04-27-09.55.14.464777 *LOCAL.DB2INST1.160427014639 select * from employee where empno > 150
2 record(s) selected.
db2 "drop THRESHOLD DBMAXCOST_COLLECT"
db2 "drop event monitor WLM_EVENT"
db2 "drop table WLM_EVENT"
db2 "drop table WLM_EVENT_STMT"
db2 "drop table WLM_EVENT_VALS"
db2 "drop table WLM_EVENT_CONTROL"