1. 通过ACTIVITY_DB2ACTIVITIES表获取SQL信息
需要知道的信息：Cognos Report的名称
然后通过
select *
from ACTIVITY_DB2ACTIVITIES A
where A.TPMON_CLIENT_APP like '%reportname%'
获取希望监控的AGENT_ID
接下来，再通过AGENT_ID获取系列SQL。
select
A.AGENT_ID,
A.QUERY_COST_ESTIMATE,
A.ROWS_RETURNED,
A.TIME_STARTED,
A.TIME_COMPLETED,
A.TIME_COMPLETED -A.TIME_STARTED as RUNNINGTIME,
B.STMT_TEXT -- 需要获取的SQL
from ACTIVITY_DB2ACTIVITIES A, ACTIVITYSTMT_DB2ACTIVITIES B
where
A.APPL_ID = B. APPL_ID
AND A.UOW_ID = B.UOW_ID
AND A.ACTIVITY_ID = B.ACTIVITY_ID
AND A.ACTIVITY_TYPE= 'READ_DML'
and A.AGENT_Id=agent_id
2. 通过表sysibmadm.mon_current_sql获取SQL。
同样的，如果是多用户并发使用该报表，需要获取需要监控的application handle，然后再查询。如下的例子是监控某一个report的所有handles。
SELECT
APPLICATION_HANDLE,
total_cpu_time / 1000000 total_cpu_sec,
query_cost_estimate ,
session_auth_id,
application_name,
client_applname,
elapsed_time_sec,
activity_state,
activity_type,
rows_read,
rows_returned ,
stmt_text
FROM sysibmadm.mon_current_sql
where APPLICATION_NAME='BIBusTKServerMain' and
client_applname like '%report_name%'
ORDER BY total_cpu_time DESC
